Try using mapflo for data analysis. It makes lookups on multiple criteria easy. Get Started for Free.


INDEX and MATCH are slightly trickier than VLOOKUP, but reduce errors as they match to a specific value rather than count columns.

The INDEX function lets you define an array (a block of cells in the spreadsheet) and then return the value that is in row X and column Y of that array.

The formula is: INDEX(reference, [row_number],[column_number])

The example below returns the first row, second column of the array B2:C4 which is 7.

Rather than hard-wiring the row and column number, we can use the MATCH function to find the row that the product we are looking up is in (in this case the row that the product “Shoes” is in) and the column the Store is in (In this case “Store 2”).

The formula is: MATCH(lookup_value, lookup_array, [match_type]).

The video below is a step-by-step guide on how to use INDEX and MATCH together to lookup the number of Shoes sold in Store 2:

[INSERT VIDEO]

Mapflo