VLOOKUP in multiple columns and returning one value

[CDNWolf]

Unfortunately I could not find a reference to this problem in the forum, any help would be greatly appreciated...


I am looking to use vlookup in order to look up competitor part numbers and return the value of my part numbers.


I have a chart outlining all the information...

Column A: my part number
Column B: Competitor 1 number
Column C: Competitor 2 number
Column D: Competitor 3 number

ie:
CB-0404 FR-3214 DB-0123 PP9876

For the above sample part numbers FR-3214 or DB-0123 or PP9876 should return a value of CB-0404 (my part number)...


It seems like a straight forward formula but I am getting a value of #N/A using the following formulas...

=VLOOKUP(A2,Referances!$A$4:$J$12,1,FALSE)
=VLOOKUP(A3,parts,1,FALSE) - Parts is a named defined range


Thanks in advance for your assistance

 

[Richard Schollar]

Hi

Do you mean like this?:
<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">My Part No</td><td style=";">Supplier A</td><td style=";">Supplier B</td><td style=";">Supplier C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Unidentified Supplier Part No</td><td style=";">My Matching Part No</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">A1</td><td style=";">RT357</td><td style=";">WD1TT</td><td style=";">10RT10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">CF56UI</td><td style=";">F5</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">A2</td><td style=";">RY567</td><td style=";">PO9IL</td><td style=";">23WE444</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">23WE444</td><td style=";">A2</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">C2</td><td style=";">WS123</td><td style=";">LP901QW</td><td style=";">12QA99</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">44JH111</td><td style=";">K9</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">F5</td><td style=";">QA233</td><td style=";">CF56UI</td><td style=";">87YH001</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">K9</td><td style=";">KL000</td><td style=";">CF59YH</td><td style=";">44JH111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I25</th><td style="text-align:left">=INDEX(<font color="Blue">$A$25:$A$29,LOOKUP(<font color="Red">9.9E+307,CHOOSE(<font color="Green">{1,2,3},MATCH(<font color="Purple">H25,$B$25:$B$29,0</font>),MATCH(<font color="Purple">H25,$C$25:$C$29,0</font>),MATCH(<font color="Purple">H25,$D$25:$D$29,0</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Andrew Poulsom]

Another possibility:

=INDEX(Sheet2!A4:A12,SUMPRODUCT((Sheet2!B4:J12=A2)*(ROW(Sheet2!A4:A12)-ROW(A3))))

 

[MisterBate5]

VLOOKUP needs the column being searched to be the leftmost column in the lookup range and will only return values in columns to the right of the column being searched. You'll probably need to use index/match instead ...

And then a couple of options come to mind (I use the literal "FR-3214", but you should replace with a cell reference):

1) If using Excel 2007/2010, you could do multiple matches and use iferror to check whether a match worked: iferror(match("FR-3214",columnB,0),iferror(match("FR-3214",columnC,0) etc. Referring to this as matchingRow, you then embed that inside =index(columnA,matchingRow)

2) To avoid the multiple matches (which in larger tables could take some time), you could add a helper column E =concatenate(columnB,"|",columnC,"|",columnD), match using wildcards =match("*"&"FR-3214&"*",columnE,0) and again embed inside =index(columnA,matchingRow)

[added later]
WOW. Forget my attempt - use one of the answers from the MVPs. Personally, I think Andrew's would be faster in bigger tables as it avoids the multiple match()

 

[CDNWolf]

Thank you for your prompt replies, much appreciated. I am using Excel 2007, sorry I forgot this detail...


Richard,

I have all known competitor part numbers (6 suppliers) so I just need the search to locate the equivalent part number from our part numbers...

On sheet2 of my workbook I have a detailed listing of all the part numbers and competitors totaling about 5000 lines

On sheet 1 I have 2 columns, Competitor Part number and Our Part Number. this simple table then searches for the competitor part number in the 5000 line spreadsheet on Sheet 2 and returns the value of our part number accordingly.

In your example table there is no need for columns H or I as there are no unknown part numbers. The columns A:D are exactly what I have in my table so calculations are only required from columns B, C, D to return the value of A.

 

[CDNWolf]

Andrew,

As MisterBate5 mentioned your formula seems pretty straight forward but I do not understand the following...

=INDEX(Sheet2!A4:A12 (OK),SUMPRODUCT((Sheet2!B4:J12 (Comp Part #'s ?) =A2 (is this creating a new cell?) *(ROW(Sheet2!A4:A12)-ROW(A3))))(what is the A3 reference ?)

Thanks again for your reply.


It would be an advantage if I could submit my sample to clarify my needs so that the formulas presented would be exact cell matches...

 

[konew1]

You say that you want to return the value (price?) of your part. Where are you getting this data from? What sheet, what column?
You give 2 formula that you use:

=VLOOKUP(A2,Referances!$A$4:$J$12,1,FALSE)
=VLOOKUP(A3,parts,1,FALSE) - Parts is a named defined range.
Both of these return return column 1 of the lookup range. That will always be the item that you are searching by (ie, the contents of A2 or A3 in your formulas), unless the lookup value does not exist in the specified range. Change the "1" in the formula to the colum with the required value

 

[MisterBate5]

Here goes ... sumproduct multiplies arrays then adds the results. So Sheet2!b4:j12 is the range to be searched and =a2 is the criterion. Will return an array of cells as big as the range searched with true in the array where the criterion matched, false otherwise. The second part (with ROW(..) fills another array (same size as the first) with the row number of each cell, minus the row number of A3 - why A3, because the row for cell A4 is 4 so you need to subtract 3 to get the row number of "the first row searched". Mutliplying the true/false array by the row number array zeros the row number wherever there is a false, leaving only the row numbers where there is a true (a match). And sumproduct then adds together all the results. Assuming there is only ever one match, the sumproduct result is used to select the correct cell from A4:A12. If there was more than one match (duplicates in range B4:J12), the technique would not work.

To step by step execution of the forula, use Formulas -> Formula Auditing -> Evaluate formula.

 

[CDNWolf]

Almost there...

Ok, I have used the formula suggested by ANDREW POULSOM with some success however as I copy the forula down the page I have lookup issues...

Line 2
=INDEX(Referances!A4:A1572,SUMPRODUCT((Referances!B4:J1572=A2)*(ROW(Referances!A4:A1572)-ROW(A3)))) (Works VERY well

Line 16 (copied down)
=INDEX(Referances!A18:A1586,SUMPRODUCT((Referances!B18:J1586=A16)*(ROW(Referances!A18:A1586)-ROW(A17)))) (if I enter the same reference number as LINE 2 I will not get an answer, just VALUE)

Please keep in mind my reference table/spreadsheet has my part numbers in column A4 to A1572 and the competitor part numbers are in columns b4 to j1572...

Thanks for all of your ongoing assistance...

 

[Andrew Poulsom]

To copy down:

=INDEX(Referances!A$4:A$1572,SUMPRODUCT((Referances!B$4:J$1572=A2)*(ROW(Referances!A$4:A$1572)-ROW(A$3))))

The $ signs make the row references absolute.