Quick, clean, and to the point

Multi-criteria lookup and transpose

Excel formula: Multi-criteria lookup and transpose
Generic formula 

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:


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


The core of this formula is INDEX, which is retrieving a value from the named range "amount" (B5:B13):


where row_num is worked out with the MATCH function and some boolean logic:


In this snippet, the location in F5 is compared with all locations, and the date in G4 is compared with all dates. The result in each case is an array of TRUE and FALSE values. When these arrays are multiplied together, the math operation coerces the TRUE and FALSE values to one's and zeros, so that the lookup array going into MATCH looks like this:


MATCH is set up to match 1 as an exact match, and returns the position to INDEX as a row number. The number 1 works for the lookup value because the array now contains only 1's and 0's, as shown above.

F5 and G4 are entered as mixed references so that the formula can be copied through the table without modification.

Transpose with paste special

If you just need to transpose a table one time, don't forget you can use paste special.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.