Index and Match with Multiple Criteria

[matthewlouis]

I am trying to match 3 rows of data with data in another tab ('1'). For some reason, some of the returned values are correct . . . but some are returning the results from a different row.

{=INDEX('1'!$R$5:$R$55,MATCH(C71&D71&E71,'1'!$A$5:$A$55&'1'!$B$5:$B$55&'1'!$C$5:$C$55),0)}

When it looks in '1'!$R$5:$R$55, it returns the correct information for some of these . . . but sometimes, it returns data that is 5 or 6 rows off.

For example, c71=7, D71=3, and E71=4. It looks in a row in '1'! for those numbers and matches the number of times that combination of 7 3 4 occurs. I made sure the C matches the $A$, the D matches the $B$, and the E matches the $C$.

In some of them, it matches correctly . . . some it does not. It always returns the right number but it is not the right row. There are no decimals and I made sure all were numbers, not text.

Anyone know why it's matching the wrong rows?

Thanks in advance!

 

[Fluff]

Try adding this
=INDEX('1'!$R$5:$R$55,MATCH(C71&D71&E71,'1'!$A$5:$A$55&'1'!$B$5:$B$55&'1'!$C$5:$C$55,0),0)
which specifies an exact match

 

[matthewlouis]

Perfect, Fluff! That nailed it. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback