How do I use lookup for this case ?

[Serhat Cengen]

I have an excel file with 5 different sheets. I want to check sheets 1..4
against there is a match for my data in sheet 5. How do I do this ? What
happens if there are multiple matches ? How do I avoid this multiple match
problem ?

 

[Pete_UK]

Give some more details of what you want to achieve and how your data
is laid out.

To avoid multiple matches, you will have to give each entry some
unique reference, eg:

=IF(A1="","",A1&"_"&COUNTIF(A$1:A1,A1))

This will add a sequential number to the data in column A wherever
there is a duplicate. You could include the sheet number in this
formula to make each entry completely unique. A formula in Sheet5 can
then cycle through each of the sheets 1 to 4 looking for a unique
match to this reference.

Hope this helps.

Pete