Lookup 2 values to return 1 value

**Domenic** · 11-19-2008, 07:30 PM

Try...

G8, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX(A!$H$2:$H$83,MATCH(1,IF(A!$A$2:$A$83=$B8,IF(A!$B$2:$B$83=G$6,1)),0))

Hope this helps!

**mewingkitty** · 11-19-2008, 08:16 PM

As usual, I found a clumsy way to make it work, but it's not 2003 compatible. I'll post it on the off chance you have 2007.

Anyways, about Vlookup, the part that threw me off while trying to use it is the column reference.

ie.

=VLOOKUP(C10,A2:B83,2,FALSE)

will take the information in C10, and look for a match for it in the left-most column of A2:B83 (It always searches the first, aka leftmost column of the selected area for matching data). It will then return information found in the 2nd column of that highlighted area (so if it found a match for C10 in cell A25, it would show the information in B25). The FALSE means it's looking for an exact match, and not an approximate one.

Hope that helps!

**TheBear** · 11-19-2008, 08:25 PM

Domenic hi..I copied your function into G8 and confirmed (}) but the dreaded #N/A appeared - did this happen when you did it?

mewingkitty..thanks for your help, unfortunately I am using a 2003 version.

**martindwilson** · 11-20-2008, 07:00 AM

you have real numbers not text numbers in g6:l6
the rest of the two sheets are text numbers so you wont find a match

**mewingkitty** · 11-20-2008, 03:40 PM

Cause with

=IFERROR(IF(EXACT(VLOOKUP($B85,A!$A$2:$B$83,2,FALSE),J$6)=TRUE,"X",""),"")

I get the attached picture as a result. Matches across the board.

**TheBear** · 11-20-2008, 09:00 PM

martindWilson - thanks for that observation I have now changed the formatting of the cells on both sheets to 'number', unfortunately I still can not get it to match!

mewingkitty - thanks for sending your screenshot, when I try your error check I can not get that to work either, I am not sure what I am doing wrong; do you think it is a compatibility error as I am still using 2003?

Thanks for your help
Glen