Advance Lookup Functions

[Kim]

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.

 

[Luke M]

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,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)

 

[Kim]

Hi Luke,

Yes I'm using Excel 2007.

I'm doing a simple example. If I want to match more, I just need to change
the number? And what if my match is not from the begining.

Example:
Sheet 1 - Marriott Marble Arch
Sheet 2 - Marble Arch Marriott

Regards,
Kim

 

[Kim]

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

 

[Jim Thomlinson]

What you are dealing with is unstrucured data. Any formulas that we can give
you are not going to work too well as your lookups are too fuzzy. You are
best off to attack the sheet in pieces and structure the data as much as
possible. For example using the search / find functions find all of the
Marriot hotels and add references that match your first sheet.

Standardize the easiest things first like country and city. Then group those
together to make standardizing hotel names and such easier. Personal names
will be the most difficult as you may need to match Will Smith with Bill
Smith with W. Smith with B. Smith.