Problem using IF(ISERROR(VLookup(... to see if text matches

[lbbrown]

I have a file with a column (column Z) of room numbers, and I am trying to use a Vlookup in column AA to see if the room number is listed in column A of a tab in the same file named "RoomList". If the room number is in column A, then I want it to display Keep, and if not, display Delete.

=IF(ISERROR(VLOOKUP(Z2,RoomList!A:A,1, TRUE)),"Keep","Delete")

It is only displaying Delete for everything, although I know many of the rooms numbers match.

Any help would be much appreciated.

Thanks

[#2]

@lbbrown 

Almost right. Try it this way:

=IF(ISERROR(VLOOKUP(Z2,RoomList!A:A,1,FALSE)),"Delete","Keep")

Using TRUE in Vlookup almost always returns a match. E.g. if you have two room numbers in the list, say 100 and 110 and you lookup 105, TRUE returns 100. Lookup 120 and you'll get 110. It picks the nearest number not greater than your lookup value. If you would lookup 90, however, you'll get #NA! So, if you are not searching for room numbers that are lower than the ones in your list, your formula never returns an ERROR and thus always picks the second option "DELETE" from your IF-statement.

Using FALSE, on the other hand, always forces Vlookup to search for an exact match.

[#3]

@Riny_van_Eekelen 

Thanks so much. That worked like a charm.