Excel Vlookup / match duplicate values

0
votes

I have two tables: Table 1 has 2 columns, PN and BL, where PN has duplicate values and BL does not have duplicate values. Table 2 has 4 columns, PN, BL1, BL2, BL3, where PN has also duplicate values and BL's do not have deplicates. I need to match the values of BL in Table 1 with the values found in BL1, BL2, and BL3 in Table 2, and then to populate a column called Answer (with the found match values).

I am trying to use vlookup, but I do not know how to use it for duplicate values.

The second point is, the values populated in the column Answer(MATCH) must be related to each row PN of Table 2 (that's why the column Answer will be created immediately after the column BL3).

The third point is if no match found, the column Answer can have empty cells.

The fourth condition is there will not have more than one match per cell because BL's are unique values.

I stuck on thinking on how to combine vlookup with a match, index, if(and or), etc.

Could you help me please with a formula? Thank you.

1
excelduplicatesmatchvlookup
"A picture is worth a thousand words". Please edit the post and provide a visual mockup of the data and its expected return. – Scott Craner
VLOOKUP will only pick first value, and your data has to be unique. Do you have any other variable in the data such as Date ? – bp41

1 Answers

0
votes

Add Three SUMIFS:

=SUMIFS(Table1[BL],Table1[BL],Table2[@BL1],Table1[PN],Table2[@PN]) +
 SUMIFS(Table1[BL],Table1[BL],Table2[@BL2],Table1[PN],Table2[@PN]) +
 SUMIFS(Table1[BL],Table1[BL],Table2[@BL3],Table1[PN],Table2[@PN])