From the course: Google Sheets: Advanced Formulas and Functions

Look up values based on two criteria - Google Sheets Tutorial

From the course: Google Sheets: Advanced Formulas and Functions

Start my 1-month free trial

Look up values based on two criteria

“

- [Narrator] In the previous movie I showed you how to create a flexible and powerful lookup formula using index and mav. In this movie I'll extend that approach and show you how to do a lookup from rectangular cell range using the same two functions. The scenario for this worksheet is that I am tracking new customers by year and region. So in this case I have the years 2013 through 2017, and the regions East, North, South and West. Now that order of regions might seem odd to English speakers because normally we say North, South, East and West. Or we might even say North, East, West and South which the first letter of the words spells NEWS. But North, South, East and West is more common. The reason that I went with East, North, South, West and 2013, 2014, 2015, 2016, 2017 is that the labels within the rangers must be sorted in ascending order. So if I had North, South, East and West and I were to look for East, then I would get an error or an incorrect value. So that means I need to have those labels sorted in ascending order same as the years. So if you're getting an error or unexpected result, look for that first. Okay, now on to the formula itself. I'll type an equal sign and cell j4, and then index. From the formula help that pops up, we see that index returns the content of cell specified by row and column offset. So rather than use just the row offset and ignoring the column offset, we're going to use two match functions. One to look up the index number of the year, and the other of the region. So I'll type a left parenthesis. We are looking up the number of new customers and that date of range is in cells D4 through G8, then a comma. Now for the row offset. That's match, left parenthesis. The year we're going to use is in cell J2, then a comma. And the range is cells C4 through C8. Right parenthesis is for the first match and then a comma. Now the second match, this will give us the column offset. Left parenthesis. Search key is J3, the cell that currently contains South, then a comma. And the range is D3 through G3. Again with those values in ascending order. Type a right parenthesis to close the second match. Right parenthesis to close index. My formula is done so I press enter. And I get the value of 136. If I want to make sure that this isn't just a fluke, and that it's working through some unseen trick or I didn't create the formula correctly, I'll change the value in J2, the year to 2017, and enter. And I get 429 which is correct and in cell J3 I'll type East and enter. And I get the value 266. So everything is working properly. And again this is the third time I've said it, but it's extremely important. If you're getting an error, make sure that all of your labels are sorted in ascending order.

Contents