Reverse Lookup question

[DAustin]

I have a complex Lookup question that I can't solve ... can anyone help!?
(without resorting to macros). It's almost like a reverse of normal Lookups...

My 'Data' worksheet contains hundreds of rows. For simplicity here, I've
just shown a few rows from one column (let's say the following is in cells
A1:A6):

Acct nbr.txt
Cust Name.xls
Notes.txt
All acct nbrs.txt
Cust nbr.xls
Ac number.txt


The 'Lookup' worksheet contains a 2 column table like this (again I've shown
only a few rows, so let's say the following is in cells A1:B4):

Acct n Account number
ac n Account number
cust Customer Name
cust nb Customer Number


What I need in cells B1:B6 of 'Data' is for it to check each value in column
A of 'Lookup', and if that value is WITHIN the value in column A of 'Data',
to display the corresponding value from column B of 'Lookup'. It should be
case insensitive, and also show an error if there are 2 matches. Examples
should help explain!... below is a snapshot of how the A1:B5 of 'Data' should
end up, based on the 'Lookup' data above:

Acct nbr.txt Account number
Cust Name.xls Customer Name
Notes.txt
All acct nbrs.txt Account number
Cust nbr.xls [Error]
Ac number.txt Account number


I.e. for most of these it found a single match, except Notes.txt (there was
no match), and Cust nbr.xls (returned an Error because in the 'Lookup' table,
both 'cust' and 'cust nb' were matches).

Hope it makes sense ... would love a good answer!

 

[T. Valko]

would love a good answer!

Unfortunately, I don't think you'll get one. (although, I've been wrong
before!)

You have too many "fuzzy" variables for matching using a formula. This would
even be a difficult task using VBA.

My recommendation is to add every possible iteration of the variable to your
lookup table. That probably "bites", but there you go!

 

[Lori]

To get the lookup value for a match you could fill down from B1 on the Data
sheet:

=LOOKUP(99,SEARCH(Lookup!A$1:A$4,A1),Lookup!B$1:B$4))

However if there is more than one match this will return the last one.
To check if there are zero, one or multiple matches you could fill down
beside this:

=COUNT(SEARCH(Lookup!A$1:A$4,A1))

i think these could be combined into one formula using IF statements to
produce the return values you want.

 

[Glenn]

I have a complex Lookup question that I can't solve ... can anyone help!?
(without resorting to macros). It's almost like a reverse of normal Lookups...

My 'Data' worksheet contains hundreds of rows. For simplicity here, I've
just shown a few rows from one column (let's say the following is in cells
A1:A6):

Acct nbr.txt
Cust Name.xls
Notes.txt
All acct nbrs.txt
Cust nbr.xls
Ac number.txt


The 'Lookup' worksheet contains a 2 column table like this (again I've shown
only a few rows, so let's say the following is in cells A1:B4):

Acct n Account number
ac n Account number
cust Customer Name
cust nb Customer Number


What I need in cells B1:B6 of 'Data' is for it to check each value in column
A of 'Lookup', and if that value is WITHIN the value in column A of 'Data',
to display the corresponding value from column B of 'Lookup'. It should be
case insensitive, and also show an error if there are 2 matches. Examples
should help explain!... below is a snapshot of how the A1:B5 of 'Data' should
end up, based on the 'Lookup' data above:

Acct nbr.txt Account number
Cust Name.xls Customer Name
Notes.txt
All acct nbrs.txt Account number
Cust nbr.xls [Error]
Ac number.txt Account number


I.e. for most of these it found a single match, except Notes.txt (there was
no match), and Cust nbr.xls (returned an Error because in the 'Lookup' table,
both 'cust' and 'cust nb' were matches).

Hope it makes sense ... would love a good answer!

Using Lori's response as a starting point, the following array formula (commit
with CTRL+SHIFT+ENTER) should do the trick:


=CHOOSE(MIN(2,COUNT(SEARCH(Lookup!A$1:A$4,A1)))+1,
"",LOOKUP(99,SEARCH(Lookup!A$1:A$4,A1),Lookup!B$1:B$4),"[Error]")

 

[DAustin]

I'm very impressed! ... thank-you Lori and Glenn for your combined work on
that. Using it on about 2000 rows means it takes a while recalculating on
changes, so I will have a look into it a little more, and if it causes too
much grief return to the macro option. Thanks!

I have a complex Lookup question that I can't solve ... can anyone help!?
(without resorting to macros). It's almost like a reverse of normal Lookups...

My 'Data' worksheet contains hundreds of rows. For simplicity here, I've
just shown a few rows from one column (let's say the following is in cells
A1:A6):

Acct nbr.txt
Cust Name.xls
Notes.txt
All acct nbrs.txt
Cust nbr.xls
Ac number.txt


The 'Lookup' worksheet contains a 2 column table like this (again I've shown
only a few rows, so let's say the following is in cells A1:B4):

Acct n Account number
ac n Account number
cust Customer Name
cust nb Customer Number


What I need in cells B1:B6 of 'Data' is for it to check each value in column
A of 'Lookup', and if that value is WITHIN the value in column A of 'Data',
to display the corresponding value from column B of 'Lookup'. It should be
case insensitive, and also show an error if there are 2 matches. Examples
should help explain!... below is a snapshot of how the A1:B5 of 'Data' should
end up, based on the 'Lookup' data above:

Acct nbr.txt Account number
Cust Name.xls Customer Name
Notes.txt
All acct nbrs.txt Account number
Cust nbr.xls [Error]
Ac number.txt Account number


I.e. for most of these it found a single match, except Notes.txt (there was
no match), and Cust nbr.xls (returned an Error because in the 'Lookup' table,
both 'cust' and 'cust nb' were matches).

Hope it makes sense ... would love a good answer!

Using Lori's response as a starting point, the following array formula (commit
with CTRL+SHIFT+ENTER) should do the trick:


=CHOOSE(MIN(2,COUNT(SEARCH(Lookup!A$1:A$4,A1)))+1,
"",LOOKUP(99,SEARCH(Lookup!A$1:A$4,A1),Lookup!B$1:B$4),"[Error]")

 

[DAustin]

So I'm already impressed with the answer, and have worked out Glenn's logic,
but Lori your part has me confused. I don't understand what your LOOKUP is
finding? (An answer isn't essential since it works, just if you can explain
easily as am naturally curious about the logic!)

To get the lookup value for a match you could fill down from B1 on the Data
sheet:

=LOOKUP(99,SEARCH(Lookup!A$1:A$4,A1),Lookup!B$1:B$4))

However if there is more than one match this will return the last one.
To check if there are zero, one or multiple matches you could fill down
beside this:

=COUNT(SEARCH(Lookup!A$1:A$4,A1))

i think these could be combined into one formula using IF statements to
produce the return values you want.

I have a complex Lookup question that I can't solve ... can anyone help!?
(without resorting to macros). It's almost like a reverse of normal Lookups...

My 'Data' worksheet contains hundreds of rows. For simplicity here, I've
just shown a few rows from one column (let's say the following is in cells
A1:A6):

Acct nbr.txt
Cust Name.xls
Notes.txt
All acct nbrs.txt
Cust nbr.xls
Ac number.txt


The 'Lookup' worksheet contains a 2 column table like this (again I've shown
only a few rows, so let's say the following is in cells A1:B4):

Acct n Account number
ac n Account number
cust Customer Name
cust nb Customer Number


What I need in cells B1:B6 of 'Data' is for it to check each value in column
A of 'Lookup', and if that value is WITHIN the value in column A of 'Data',
to display the corresponding value from column B of 'Lookup'. It should be
case insensitive, and also show an error if there are 2 matches. Examples
should help explain!... below is a snapshot of how the A1:B5 of 'Data' should
end up, based on the 'Lookup' data above:

Acct nbr.txt Account number
Cust Name.xls Customer Name
Notes.txt
All acct nbrs.txt Account number
Cust nbr.xls [Error]
Ac number.txt Account number


I.e. for most of these it found a single match, except Notes.txt (there was
no match), and Cust nbr.xls (returned an Error because in the 'Lookup' table,
both 'cust' and 'cust nb' were matches).

Hope it makes sense ... would love a good answer!

 

[Glenn]

Use Tools / Formula Auditing / Evaluate Formula to step through how Excel gets
the answer.

SEARCH(Lookup!A$1:A$4,A1) returns an array of values corresponding to the
position where each value within Lookup!A$1:A$4 is found in A1. If not found,
#VALUE! is returned.

Assuming A1 = "Formula Testing" and Lookup!A$1:A$4 = {"this", "test", "is",
"true"}, SEARCH() will return {#VALUE!, 9, #VALUE!, #VALUE!}

The key part of the LOOKUP() function is this:

If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value.

So I'm already impressed with the answer, and have worked out Glenn's logic,
but Lori your part has me confused. I don't understand what your LOOKUP is
finding? (An answer isn't essential since it works, just if you can explain
easily as am naturally curious about the logic!)

To get the lookup value for a match you could fill down from B1 on the Data
sheet:

=LOOKUP(99,SEARCH(Lookup!A$1:A$4,A1),Lookup!B$1:B$4))

However if there is more than one match this will return the last one.
To check if there are zero, one or multiple matches you could fill down
beside this:

=COUNT(SEARCH(Lookup!A$1:A$4,A1))

i think these could be combined into one formula using IF statements to
produce the return values you want.

I have a complex Lookup question that I can't solve ... can anyone help!?
(without resorting to macros). It's almost like a reverse of normal Lookups...

My 'Data' worksheet contains hundreds of rows. For simplicity here, I've
just shown a few rows from one column (let's say the following is in cells
A1:A6):

Acct nbr.txt
Cust Name.xls
Notes.txt
All acct nbrs.txt
Cust nbr.xls
Ac number.txt


The 'Lookup' worksheet contains a 2 column table like this (again I've shown
only a few rows, so let's say the following is in cells A1:B4):

Acct n Account number
ac n Account number
cust Customer Name
cust nb Customer Number


What I need in cells B1:B6 of 'Data' is for it to check each value in column
A of 'Lookup', and if that value is WITHIN the value in column A of 'Data',
to display the corresponding value from column B of 'Lookup'. It should be
case insensitive, and also show an error if there are 2 matches. Examples
should help explain!... below is a snapshot of how the A1:B5 of 'Data' should
end up, based on the 'Lookup' data above:

Acct nbr.txt Account number
Cust Name.xls Customer Name
Notes.txt
All acct nbrs.txt Account number
Cust nbr.xls [Error]
Ac number.txt Account number


I.e. for most of these it found a single match, except Notes.txt (there was
no match), and Cust nbr.xls (returned an Error because in the 'Lookup' table,
both 'cust' and 'cust nb' were matches).

Hope it makes sense ... would love a good answer!

 

[T. Valko]

The key part of the LOOKUP() function is this:

If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value.

If LOOKUP can't find the lookup_value it matches the *last* value in the
lookup_vector that is less than or equal to lookup_value.

The resulting array could look like this:

{#VALUE!, 9, 2, #VALUE!}

The *last* value that is less than the lookup_value would be 2.

--
Biff
Microsoft Excel MVP

Use Tools / Formula Auditing / Evaluate Formula to step through how Excel
gets the answer.

SEARCH(Lookup!A$1:A$4,A1) returns an array of values corresponding to the
position where each value within Lookup!A$1:A$4 is found in A1. If not
found, #VALUE! is returned.

Assuming A1 = "Formula Testing" and Lookup!A$1:A$4 = {"this", "test",
"is", "true"}, SEARCH() will return {#VALUE!, 9, #VALUE!, #VALUE!}

The key part of the LOOKUP() function is this:

If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value.

So I'm already impressed with the answer, and have worked out Glenn's
logic, but Lori your part has me confused. I don't understand what your
LOOKUP is finding? (An answer isn't essential since it works, just if you
can explain easily as am naturally curious about the logic!)

To get the lookup value for a match you could fill down from B1 on the
Data sheet:

=LOOKUP(99,SEARCH(Lookup!A$1:A$4,A1),Lookup!B$1:B$4))

However if there is more than one match this will return the last one.
To check if there are zero, one or multiple matches you could fill down
beside this:

=COUNT(SEARCH(Lookup!A$1:A$4,A1))

i think these could be combined into one formula using IF statements to
produce the return values you want.

:

I have a complex Lookup question that I can't solve ... can anyone
help!? (without resorting to macros). It's almost like a reverse of
normal Lookups...

My 'Data' worksheet contains hundreds of rows. For simplicity here,
I've just shown a few rows from one column (let's say the following is
in cells A1:A6):

Acct nbr.txt Cust Name.xls Notes.txt
All acct nbrs.txt Cust nbr.xls Ac number.txt

The 'Lookup' worksheet contains a 2 column table like this (again I've
shown only a few rows, so let's say the following is in cells A1:B4):

Acct n Account number
ac n Account number
cust Customer Name
cust nb Customer Number

What I need in cells B1:B6 of 'Data' is for it to check each value in
column A of 'Lookup', and if that value is WITHIN the value in column A
of 'Data', to display the corresponding value from column B of
'Lookup'. It should be case insensitive, and also show an error if
there are 2 matches. Examples should help explain!... below is a
snapshot of how the A1:B5 of 'Data' should end up, based on the
'Lookup' data above:

Acct nbr.txt Account number
Cust Name.xls Customer Name
Notes.txt
All acct nbrs.txt Account number
Cust nbr.xls [Error]
Ac number.txt Account number


I.e. for most of these it found a single match, except Notes.txt (there
was no match), and Cust nbr.xls (returned an Error because in the
'Lookup' table, both 'cust' and 'cust nb' were matches).

Hope it makes sense ... would love a good answer!

 

[Glenn]

If LOOKUP can't find the lookup_value it matches the *last* value in the
lookup_vector that is less than or equal to lookup_value.

The resulting array could look like this:

{#VALUE!, 9, 2, #VALUE!}

The *last* value that is less than the lookup_value would be 2.

Right, which was the reason for preceding the LOOKUP() with a COUNT():

=CHOOSE(MIN(2,COUNT(SEARCH(Lookup!A$1:A$4,A1)))+1,
"",LOOKUP(99,SEARCH(Lookup!A$1:A$4,A1),Lookup!B$1:B$4),"[Error]")