is there a way to search with vlookup to match more than 1 column
I know how to use vlookup() but I have found a new problem.
Say I have a BIG sheet and want to search it for MORE THAN ONE match in multiple columns. Further explination: I do a vlookup() of the table, but there are multiple matches with the first paramater. So a second paramater is given and so from the first match, I want a second match made. Is this possible? thanks much in advance. |
You can only get one return using a VLOOKUP. But, you can get tricky and
use CONCATENATION to create a new lookup column and then look up CONCATENATED combinations........... something like........... =VLOOKUP(A1&B1,D:E,2,FALSE) where column D has the CONCATENATED value combinations you're looking for............ Vaya con Dios, Chuck, CABGx3 "puppy" wrote in message ... I know how to use vlookup() but I have found a new problem. Say I have a BIG sheet and want to search it for MORE THAN ONE match in multiple columns. Further explination: I do a vlookup() of the table, but there are multiple matches with the first paramater. So a second paramater is given and so from the first match, I want a second match made. Is this possible? thanks much in advance. |
I like this syntax:
=index(othersheet!$c$1:$c$10, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) puppy wrote: I know how to use vlookup() but I have found a new problem. Say I have a BIG sheet and want to search it for MORE THAN ONE match in multiple columns. Further explination: I do a vlookup() of the table, but there are multiple matches with the first paramater. So a second paramater is given and so from the first match, I want a second match made. Is this possible? thanks much in advance. -- Dave Peterson |
Sounds like you're looking for the Sumproduct() function.
*Not* how it was designed by MS (see Help Files), but as it's being used today. Check out this link: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "puppy" wrote in message ... I know how to use vlookup() but I have found a new problem. Say I have a BIG sheet and want to search it for MORE THAN ONE match in multiple columns. Further explination: I do a vlookup() of the table, but there are multiple matches with the first paramater. So a second paramater is given and so from the first match, I want a second match made. Is this possible? thanks much in advance. |
OR, you could combine elements of both Chuck's and Dave's examples and does
not need a concatenated helper column. =INDEX(range,MATCH(A1&B1,AA1:AA10&AB1:AB10,0)) Entered as an array with the key combo of CTRL,SHIFT,ENTER. Biff "puppy" wrote in message ... I know how to use vlookup() but I have found a new problem. Say I have a BIG sheet and want to search it for MORE THAN ONE match in multiple columns. Further explination: I do a vlookup() of the table, but there are multiple matches with the first paramater. So a second paramater is given and so from the first match, I want a second match made. Is this possible? thanks much in advance. |
I should have added this:
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Dave Peterson wrote: I like this syntax: =index(othersheet!$c$1:$c$10, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) puppy wrote: I know how to use vlookup() but I have found a new problem. Say I have a BIG sheet and want to search it for MORE THAN ONE match in multiple columns. Further explination: I do a vlookup() of the table, but there are multiple matches with the first paramater. So a second paramater is given and so from the first match, I want a second match made. Is this possible? thanks much in advance. -- Dave Peterson -- Dave Peterson |
But if your data looks like:
aaa bbbb aaab bbb a aabbbb It could yield the incorrect results. =INDEX(range,MATCH(A1&char(10)&B1,AA1:AA10&char(10 )&AB1:AB10,0)) (using alt-enter as a separator--if it's not used within the strings.) Biff wrote: OR, you could combine elements of both Chuck's and Dave's examples and does not need a concatenated helper column. =INDEX(range,MATCH(A1&B1,AA1:AA10&AB1:AB10,0)) Entered as an array with the key combo of CTRL,SHIFT,ENTER. Biff "puppy" wrote in message ... I know how to use vlookup() but I have found a new problem. Say I have a BIG sheet and want to search it for MORE THAN ONE match in multiple columns. Further explination: I do a vlookup() of the table, but there are multiple matches with the first paramater. So a second paramater is given and so from the first match, I want a second match made. Is this possible? thanks much in advance. -- Dave Peterson |
Good point!
Biff "Dave Peterson" wrote in message ... But if your data looks like: aaa bbbb aaab bbb a aabbbb It could yield the incorrect results. =INDEX(range,MATCH(A1&char(10)&B1,AA1:AA10&char(10 )&AB1:AB10,0)) (using alt-enter as a separator--if it's not used within the strings.) Biff wrote: OR, you could combine elements of both Chuck's and Dave's examples and does not need a concatenated helper column. =INDEX(range,MATCH(A1&B1,AA1:AA10&AB1:AB10,0)) Entered as an array with the key combo of CTRL,SHIFT,ENTER. Biff "puppy" wrote in message ... I know how to use vlookup() but I have found a new problem. Say I have a BIG sheet and want to search it for MORE THAN ONE match in multiple columns. Further explination: I do a vlookup() of the table, but there are multiple matches with the first paramater. So a second paramater is given and so from the first match, I want a second match made. Is this possible? thanks much in advance. -- Dave Peterson |
All times are GMT +1. The time now is 01:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com