# Thread: Index formula with two criteria using two sheets

1. ## Index formula with two criteria using two sheets

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)

Thanks,

Jim

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

3. 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))

4. Originally Posted by sambit
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. 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. 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
•