Vlookup multiple lookup values
- Thread starter sguna1982
- Start date
My recommendation would be to have all the Client Codes (lookup values) in a single column, but if that can not be helped, with your example workbook, formula in cell I3 could be:
=SUMPRODUCT(ISNUMBER(SEARCH("|"&G3,"|"&$A$3:$A$10&"|"&$B$3:$B$10&"|"&$C$3:$C$10))*$D$3:$D$10)
=SUMPRODUCT(ISNUMBER(SEARCH("|"&G3,"|"&$A$3:$A$10&"|"&$B$3:$B$10&"|"&$C$3:$C$10))*$D$3:$D$10)
Sure thing. HEre's the whole formula, with color added for explanation
=SUMPRODUCT(ISNUMBER(SEARCH("|"&G3,"|"&$A$3:$A$10&"|"&$B$3:$B$10&"|"&$C$3:$C$10))*$D$3:$D$10)
Let's look at the blue part first. In order to look at all 3 of the columns of client codes at the same time, to see which one contains the value we want, we concatenate them together. However, we also want to make sure that by concatentating two unique values, we don't accidentally create a third (e.g., if I'm looking fof baseball, and one list had "base" and the next had "ball", don't want to put them together and accidentally make "baseball". So, before each column we add a pipe symbol, something that 99.9% of the time doesn't occur naturally in data.
Now that we have a searchable array, we look at the red part. This is where we state what to look for. Since we added pipe symbols to our search arrach, we concatenate a pipe symbol to the lookup value. Now, our SEARCH function will produce an array, with either an error (saying that the value wasn't found) or a number (indicating position within value that our search value was found). Example of what that looks like:
{#N/A!,#N/A!,#N/A!,5#N/A!}
This gets fed to the ISNUMBER function, which converts this ugly thing into an array with just TRUE/FALSE values.
{False,False,False,True,False}
XL also treats True/False = 1/0.
{0,0,0,1,0}
NOTE: I made an assumption at this point that the search value was only found in one place. You'll see why this is important in next step.
The SUMPRODUCT then takes that array and multiplies it against our values, the percentages. So, if the Green array looked like:
{10,20,30,40,50}
when we mutiply it against the 1/0 array we built, we'll get:
{0,0,0,40,0}
and then finally SUMPRODUCT spits out the sum of that array, which is 40. For our assumption, if 2 rows had the search value, then 2 different percentages would have been fed into the SUM, and our answer would be wrong.
=SUMPRODUCT(ISNUMBER(SEARCH("|"&G3,"|"&$A$3:$A$10&"|"&$B$3:$B$10&"|"&$C$3:$C$10))*$D$3:$D$10)
Let's look at the blue part first. In order to look at all 3 of the columns of client codes at the same time, to see which one contains the value we want, we concatenate them together. However, we also want to make sure that by concatentating two unique values, we don't accidentally create a third (e.g., if I'm looking fof baseball, and one list had "base" and the next had "ball", don't want to put them together and accidentally make "baseball". So, before each column we add a pipe symbol, something that 99.9% of the time doesn't occur naturally in data.
Now that we have a searchable array, we look at the red part. This is where we state what to look for. Since we added pipe symbols to our search arrach, we concatenate a pipe symbol to the lookup value. Now, our SEARCH function will produce an array, with either an error (saying that the value wasn't found) or a number (indicating position within value that our search value was found). Example of what that looks like:
{#N/A!,#N/A!,#N/A!,5#N/A!}
This gets fed to the ISNUMBER function, which converts this ugly thing into an array with just TRUE/FALSE values.
{False,False,False,True,False}
XL also treats True/False = 1/0.
{0,0,0,1,0}
NOTE: I made an assumption at this point that the search value was only found in one place. You'll see why this is important in next step.
The SUMPRODUCT then takes that array and multiplies it against our values, the percentages. So, if the Green array looked like:
{10,20,30,40,50}
when we mutiply it against the 1/0 array we built, we'll get:
{0,0,0,40,0}
and then finally SUMPRODUCT spits out the sum of that array, which is 40. For our assumption, if 2 rows had the search value, then 2 different percentages would have been fed into the SUM, and our answer would be wrong.
Somendra Misra
Excel Ninja
Hi Guna,
If you can arrange your raw data as per attached file (Green Cells) than the extraction become easy (Blue Cells) in the attached file.
Regards,
If you can arrange your raw data as per attached file (Green Cells) than the extraction become easy (Blue Cells) in the attached file.
Regards,