Excel 2007 : Using lookup forlmula

[Deepthinaidu]

Hello All,

I'm new to excel and trying to do a compare functionality through excel n went half way.
The requirement is that, in a excel i have 4 sheets.one of the sheets' column values i have to lookup in the rest of the 3 sheets' columns.

I need to search sheet 4 'A' column (A2 to A56) values in sheet 1 column k(2,102) , sheet2 column K(2,72) & sheet3 column k(2,40).

based on this if it finds it should give 'True' or 'False'.This is how i did it.
=ISERROR(LOOKUP(A2,'sheet'!$K$2:$K$102))

I have sorted values in the K of sheet1 and used lookup to do this. it worked fine.

but in the sheet2 column k i have duplicate entries. it is not working. for some of the values which actually are not there in sheet 2 it results 'TRUE'.

Not able to get it why is it behvaing like this. and also is it possible to do lookup in multiple columns at a time? Is there any better formula to achieve this. Reqirement is very simple as of 'find' functionality but in multiple sheets and in specific columns.

Thanks in advance & Regards,
Deepthi

[JBeaucaire]

LOOKUP() requires the data to be sorted ascending and if it isn't you will get some wonky results. LOOKUP() also allows "fuzzy" matching so it will match a number to the next lowest value in the match range. This does not sound like a good thing for you.

Since what you want is an EXACT match only to return TRUE and any missing values to return an error, use the MATCH() function instead and set the 3rd parameter to FALSE to force exact matches only.

=ISERROR(MATCH(A2, 'sheet'!$K$2:$K$102, 0))

If the TRUE/FALSE results feel backward to you, use this instead:

=ISNUMBER(MATCH(A2, 'sheet'!$K$2:$K$102, 0))

[DonkeyOte]

Alternatively:

Please Login or Register  to view this content.

the above will return True for exact matches and will be quick given LOOKUP is fast - as JB states it assumes sorted data (implied)
(for the reverse logic swap = to <> and FALSE to TRUE )

Note the above is not case sensitive - if that's a requirement then let us know.