Home » Posts

Multi-criteria lookup and transpose

To perform a multi-criteria lookup and transpose results into a table, you can use an array formula based on INDEX and MATCH. In the example shown, the formula in G5 is: {=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0))} Note this formula is an array formula and must be entered with control + shift + enter. This formula also uses three named ranges: location = B5:B13, amount = D5:D13, date = C5:C13

April 14, 2025 · 1 min · 52 words · Cheryl Johnson

The result in each case is an array of TRUE and FALSE values.

F5 and G4 are entered asmixed referencesso that the formula can be copied through the table without modification.

you could use INDEX to retrieve individual values, or entire rows and columns.

Excel formula: VLOOKUP two-way lookup

MATCH supports approximate and exact matching, andwildcards(* ?)

Excel formula: Two-way lookup with INDEX and MATCH

Excel INDEX function

Excel MATCH function