Hi Luke,

Was testing the formula but it doesnt work. It give me the first ref number
when the Country is matched.

What I need is the country need to be exact match, then if have to check if
the city is match (at least 3 letters), then check the address and follow by
the name. If it match then it return the ref number.

If it doesn't match then it will be blank.

Regards,
Kim

"Luke M" wrote:

Since you mentioned having over 100k rows, I'm assuming you're using XL 2007.
Here's a start:

=INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!B:B),--(Sheet1!E:E=Sheet2!E2),--(LEFT(Sheet1!D:D,3)=LEFT(D2,3)),--(LEFT(Sheet1!B:B,5)=LEFT(B2,5)),((LEFT(C2,5)=LEFT( Sheet1!C:C,5))+ISBLANK(C2))))

A comment:
You said that name needs to match "at least 5 letters" but your example only
has 3 listed on 2nd worksheet. The current formula is written to check first
5 characters, but if you really meant only 3, it will need to be changed (the
LEFT functions dealing with column B)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kim" wrote:

I've 2 w/sheet.
One is the correct detail and with reference number
Another is a imcomplete w/sheet.

What I need is to find the reference number for 2 w/sheet if some of the
criteria match the first sheet.

Example:
W/Sheet One.
Ref Name Add City Country
123 XYX Hotel 27 W. Street NYC USA
125 ABC Hotel Hannover St LON UK

W/Sheet Two
Ref Name Add City Country
ABC LON UK

So what I need is for w/sheet two to return the reference based on sheet one
if the following criteria is match:

1. Country - Exact Match
2. City - Match at least 2-3 letters
3. Address - Match at least 5 letters (if ok if there is no value)
4. Name - Match at least 5 letters

Not sure if that's possible. I've more than 100k row in the first sheet. So
it will take ages to find one by one. Trying to find easies way.

Hope to find solution soon.

Thanks.