MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookup 'looks up' duplicate values in error


Posted by steven on January 08, 2002 12:25 AM

I have used vlookup find values from the table below

1 20 to return cloumn 2 figures for 1
2 30 2
3 40 4
4 50 7

Where 7 is not in the list, I get the duplicate of the value in column 2 above ie 1 20
2 30
4 50
7 50
using vlookup(a2,range,2,true). I realise this may be quite simple but an explanation would be of great help

Cheers Steven


Posted by Aladin Akyurek on January 08, 2002 2:17 AM

Steven --

It looks like you want an exact match.

=IF(COUNTIF(A2:A4,lookup-value),VLOOKUP(lookup-value,A2:A4,2,0),"")

will retrieve by exact matching.

Aladin

============