Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
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! :-) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
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! -- Biff Microsoft Excel MVP "DAustin" wrote in message ... 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! :-) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
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. "DAustin" wrote: 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! :-) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
DAustin wrote:
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]") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
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! "Glenn" wrote: DAustin wrote: 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]") |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
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!) "Lori" wrote: 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. "DAustin" wrote: 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! :-) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
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. DAustin wrote: 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!) "Lori" wrote: 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. "DAustin" wrote: 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! :-) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
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 "Glenn" wrote in message ... 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. DAustin wrote: 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!) "Lori" wrote: 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. "DAustin" wrote: 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! :-) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse Lookup question
T. Valko wrote:
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. 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]") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function in Reverse? | Excel Discussion (Misc queries) | |||
Reverse Lookup column only | Excel Discussion (Misc queries) | |||
Reverse Matrix lookup? | Excel Discussion (Misc queries) | |||
Reverse lookup | Excel Worksheet Functions | |||
Need help with reverse phone lookup | Excel Worksheet Functions |