Excel 2007 : INDEX and MATCH question

[jmcgallan]

Help Needed.

I am attempting to lookup in an array 2 pieces of information to give me the corresponding result. I am usinf the formula below that works fine.

=IFERROR(INDEX(Hotel!$A$2:$BV$2076,MATCH(Transfer!$A2,Hotel!$A$2:$A$2076,0),MATCH(Transfer!$C2,Hotel!$A$1:$BV$1,0)),"")

The issue is that there are multiple matches to the conditions i set, however one match always returns an emty value,see attached.

In the 'Transfer tab i want to look up the 'Primary Guest ID number' and the 'Asset Date' against the information in the 'Hotel' tab but you will notice that the ID number is displayed multiple times in the 'Hotel' tab. How do i get the formula to move onto the next match if the value is null??

[contaminated]

Hi and welcome to the forum. Hope it works for you

=INDEX(Hotel!$A$2:$BL$11,SMALL(IF(A2-Hotel!$A$2:$A$28,ROW(INDIRECT("1:"&ROWS(Hotel!$A$2:$A$28)))),COUNTIF($A$2:A2,A2)),MATCH(C2,Hotel!$A$1:$BL$1,0))

confirm w/ ctrl+shift+enter and copy down

[romperstomper]

You could also use:
=IFERROR(LOOKUP(2,1/((Hotel!$A$2:$A$11=$A2)*(INDEX(Hotel!$A$2:$BL$11,,MATCH($C2,Hotel!$A$1:$BL$1,0))<>"")),INDEX(Hotel!$A$2:$BL$11,,MATCH($C2,Hotel!$A$1:$BL$1,0))),"")

If you can store the column match in another column, it would be more efficient.

[jmcgallan]

Thanks for the replies.

As you can see in the attached i have tried both formulas however i still get blank cells.

Any other ideas??

[romperstomper]

That's because there is no data matching your criteria...

[jmcgallan]

Good point...well made!

Thanks for your help