Results 1 to 6 of 6

Thread: Index formula with two criteria using two sheets

  1. #1

    Index formula with two criteria using two sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I have two sheets, Sheet 1 displays a list of baseball batters by Name/#/Salary/H/Type/Pos/Team/Opp. Sheet 2 displays pitcher, their team and opposing team and under neath it I want to display the opposing batter's name. The two criteria for the batter are: 1. his position (Pos) needs to be center (C) and 2. is from the pitcher's opposing (Opp) team, in this case it's Arizona. I used the formula below but I am getting a #N/A.

    =INDEX(BATTERS!A:H,MATCH(1,(BATTERS!F:F='PITCHER & BATTER'!B3)*(BATTERS!A:A='PITCHER & BATTER'!C5),0),1)


    Please see the attachment.

    Thanks,

    Jim
    Attached Files Attached Files

  2. #2
    In sheet 2 I wrote I want cell A5 to... I meant cell A8.

  3. #3
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    put below array formula

    Note: Press (Ctrl+Shift+Enter) not Enter key.

    =INDEX(BATTERS!A:A,MATCH(B3&C5,BATTERS!F:F&BATTERS!G:G,0))
    Attached Files Attached Files

  4. #4
    Quote Originally Posted by sambit View Post
    Hi,
    put below array formula

    Note: Press (Ctrl+Shift+Enter) not Enter key.

    =INDEX(BATTERS!A:A,MATCH(B3&C5,BATTERS!F:F&BATTERS!G:G,0))

    Thanks sambit. Works like a charm!

    Jim

  5. #5
    Hi Sambit, actually the formula only works on one of my sheets. Instead of Welington Castillo, it's giving me a different name on my other sheet which has a list of more names.

  6. #6
    N/m I actually had the sheet names wrong on my 2nd spreadsheet. Thanks a bunch sambit!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •