Index Match Across Sheets HELP

[rck3]

Ok, I usually can figure out most issues on my own but I am stuck on this. I am able to do this on a single sheet but when I reference another sheet in the workbook it does not.

=INDEX(A3:E31,MATCH(B1&C1,A3:A31&B3:B31,0),3)

Using this formula in the data sheet I get the result I want. The issue is I want to use this in a master sheet. The master sheet will have 2 fields the user can type in a search field, Those search fields are A6 and B3. based on those 2 fields, the function will display the 3rd column. What am I missing?

The data sheet is Shelter Team Data so I tried the below. The data sheet is a table of 6 columns with the 1st two being the reference items and the next 4 my results.

=INDEX('Shelter Team Data'!A3:E31, MATCH(A6&B3,'Shelter Team Data'!B8:B36&'Shelter Team Data'!C8:C36,0), 3)

 

[T. Valko]

Ok, I usually can figure out most issues on my own but I am stuck on this. I am able to do this on a single sheet but when I reference another sheet in the workbook it does not.

=INDEX(A3:E31,MATCH(B1&C1,A3:A31&B3:B31,0),3)

Using this formula in the data sheet I get the result I want. The issue is I want to use this in a master sheet. The master sheet will have 2 fields the user can type in a search field, Those search fields are A6 and B3. based on those 2 fields, the function will display the 3rd column. What am I missing?

The data sheet is Shelter Team Data so I tried the below. The data sheet is a table of 6 columns with the 1st two being the reference items and the next 4 my results.

=INDEX('Shelter Team Data'!A3:E31, MATCH(A6&B3,'Shelter Team Data'!B8:B36&'Shelter Team Data'!C8:C36,0), 3)

Works OK for me.

Did you array enter the formula?

Are you sure about those range references? You're indexing rows 3:31 and you're matching rows 8:36.

 

[rck3]

Thank you, I had not used the formula like that prior and was so worried about the correct setup I did not notice when I pasted in the other sheet the cell reference had updated. I am good now, and feel better than I did understand, just a stupid mental mistake.

 

[rck3]

Of I have a side issue on this.

I ended up with

=INDEX('Shelter Team Data'!$A$3:$E$31, MATCH(A6&$B$3,'Shelter Team Data'!$A$3:$A$31&'Shelter Team Data'!$B$31:$B$38,0), 3)

This works fine. I then wanted to compare the same set of data against the same B3 and this time A7 so I used the below but it keeps giving me NA. I did shift ctl enter.

=INDEX('Shelter Team Data'!$A$3:$E$31, MATCH(A7&$B$3,'Shelter Team Data'!$A$3:$A$31&'Shelter Team Data'!$B$31:$B$38,0), 3)

 

[T. Valko]

Of I have a side issue on this.

I ended up with

=INDEX('Shelter Team Data'!$A$3:$E$31, MATCH(A6&$B$3,'Shelter Team Data'!$A$3:$A$31&'Shelter Team Data'!$B$31:$B$38,0), 3)

This works fine. I then wanted to compare the same set of data against the same B3 and this time A7 so I used the below but it keeps giving me NA. I did shift ctl enter.

=INDEX('Shelter Team Data'!$A$3:$E$31, MATCH(A7&$B$3,'Shelter Team Data'!$A$3:$A$31&'Shelter Team Data'!$B$31:$B$38,0), 3)

If that first formula returns the correct result then it's a case of "dumb luck".

The lookup_ array ranges need to be the same size. You have:

• A3:A31
• B31:B38

Here's the syntax that I would use to write that formula (using equal size lookup_arrays). I'm going to use a different sheet name so it'll be easier to read.

Array entered:

=INDEX(Sheet1!C3:C31,MATCH(1,IF(Sheet1!A3:A31=A7,IF(Sheet1!B3:B31=B3,1)),0))