Combining IF and INDEX functions

Hi Peter,

As a variant I may suggest to form your data in Excel Tables (available from Excel 2007). To convert the range which has table structure (i.e. headers and rows with data) just stay somewhere within the range and press Ctrl+T.

For your sample tables looks like

In LookupTable1 in first row in Brand column enter the formula

=VLOOKUP(LookupTable1[@[SKU]:[SKU]],ReferenceTable,MATCH(LookupTable1[[#Headers],[Brand]],ReferenceTable[#Headers],0),FALSE)

First part

LookupTable1[@[SKU]:[SKU]]

is kind of absolute reference which fixes your SKU column when you copy the formula to the right.

MATCH returns the index of the column in ReferenceTable which has the same header as in LookupTable.

When drag the formula to the right with Copy cells (not copy/paste) till end of your table.

Similar for the second lookup table.

That's all. No need to copy/paste and/or change your formulas when you expand your Reference table. Adding SKU into lookup table you only need to enter SKU into the next from the bottom of the lookup table line. The table will auto expands and formulas in next columns auto-fills returning you values from the Reference table.

As variant INDEX/MATCH could be used, see LookupTable3 and LookupTable4. Same result, only the formula above looks like

=INDEX(ReferenceTable,MATCH(LookupTable3[@[SKU]:[SKU]],ReferenceTable[[SKU]:[SKU]],0),MATCH(LookupTable3[[#Headers],[Brand]],ReferenceTable[#Headers],0))

Please see attached file with the sample.