Looking up a value based on one criteria
Let’s say we have a table with employees and their total sales and we want a formula to return the total sales based on the employee.
Now, if we want to get Allison’s sales, we could use a VLOOKUP formula using this formula. Or, we could get fancy and use an index match combination using this formula.
Looking up a value with a row criteria and a column criteria
Now suppose we have a similar table and the employees sales are split out between sales in the East and sales in the West. Now let’s say we need to lookup Guy’s sales for the West.
Looking up a value with two or more row criteria
Ok, what happens now if we have the same data but it’s not laid out in a two dimensional range like above (ie one lookup value going down the rows and one lookup value going across the columns)? Let’s say we have a column with the region and we’re still tasked with finding Guy’s sales for the West. In this case we can concatenate our lookup values and lookup ranges to use an array formula. When you enter this formula, instead of pressing enter in the usual way, you will need to press Ctrl + Shift + Enter. If you’ve done this correctly, then you should see a set of { } curly parenthesis around the formula in the formula bar. If we have 3rd, 4th etc… criteria in additional columns, we can use this same array function approach to lookup values.
