Problem:
Given a Master List in “Sheet1” of an MS Excel workbook
| A | B | C | D | E | |
| 1 | Unit Price | Unit Price | Unit Price | ||
| 2 | UniqueID | Commodity | ABC Store | Sari-Sari Store | XYZ Store |
| 3 | 201201 | Apple | 50.00 | 49.50 | 50.50 |
| 4 | 201202 | Cherry | 37.50 | 38.00 | 38.50 |
| 5 | 201203 | Strawberry | 26.75 | 26.00 | 26.25 |
| 6 | 201204 | Mango | 18.00 | 18.25 | 17.75 |
| 7 | 201205 | Buko | 30.00 | 29.00 | 28.00 |
You are requested to fill up the Commodity and Unit Price columns in “Sheet2” of the same workbook.
| A | B | C | D | E | F | |
| 1 | ItemID | Quantity | Commodity | ABC Store Unit Price | Sari-Sari Store Unit Price | XYZ Store Unit Price |
| 2 | 201205 | 1 | ||||
| 3 | 201203 | 3 | ||||
| 4 | 201201 | 2 | ||||
| 5 | 201204 | 5 | ||||
| 6 | 201202 | 7 | ||||
| 7 | 201203 | 9 | ||||
| 8 | 201205 | 3 | ||||
| 9 | 201201 | 5 | ||||
| 10 | 201202 | 1 |
Solution:
For C2, here is the formula
=IF(ISNA(VLOOKUP(A2,Sheet1!$A$3:Sheet1!$E$7,2,FALSE)),0,VLOOKUP(A2,Sheet1!$A$3:Sheet1!$E$7,2,FALSE))
For D2, the formula is
=IF(ISNA(VLOOKUP(A2,Sheet1!$A$3:Sheet1!$E$7,3,FALSE)),0,VLOOKUP(A2,Sheet1!$A$3:Sheet1!$E$7,3,FALSE))
Explanation:
VLOOKUP function has the following parameters:
lookup_value: this should be the ItemID which needs to be matched to the UniqueID in the Master List.
table_array: the range in the Master List where the matching and picking will be made
col_index_num: the column index of the table_array where the value will be picked.
For C2, since we are looking for the Commodity, 2 means the second column of the table_array. For D2 since we are looking for the unit price of ItemID for ABC Store, 3 means the third column of the table_array.
range_lookup: use FALSE when you need an ‘exact match’ of lookup_value and the UniqueID in Master List.
