google-sheetsconcatenationarray-formulasgoogle-sheets-formulags-vlookup

How to INDEX and MATCH with 4 Criteria

I'm using 4 criteria to determine the retail price and SKU (secondary concern). I've previously had success when using INDEX and MATCH for 2 criteria. With 4 criteria I'm running into errors

Pricelist

I've attempted to use this formula

=ArrayFormula(index(CFanPriceList,match(1,(A18=CFanStyle)*(B18=CFanColour),*(C18=CFanSize),*(D18=CFanLight),0),7))

The Named Ranges are:

CFanPrices  A2:H15 

CFanStyle   A2:A15 

CFanColour  B2:B15 

CFanSize    C2:C15 

CFanLight   D2:D15

So, in the example:

Fanco Eco Silent,   White,  48',    TRUE

I'd expect to return the figure $279

Instead, I get

ERROR! formula parse error

Any thoughts?

Solution

  • use this instead of your formula: 

    =ARRAYFORMULA(IFERROR(VLOOKUP(A18&B18&C18&D18, 
 {A1:A16&B1:B16&C1:C16&D1:D16, A1:G16}, 8, 0)))

    or with your named ranges: 

    =ARRAYFORMULA(IFERROR(VLOOKUP(A18&B18&C18&D18, 
 {CFanStyle&CFanColour&CFanSize&CFanLight, CFanPrices}, 8, 0)))