In this post, we’ll perform a two-dimensional lookup with Excel’s VLOOKUP function.

## Objective

Let’s begin by clarifying our objective and what is meant by the term two-dimensional lookup. We have stored our price list in a table, and the price for each item varies based on the region. This is illustrated in the screenshot below.

To create a quote for a customer, we’ll enter the item and the region and we want Excel to retrieve the correct price, as illustrated below.

This is considered a two-dimensional lookup because we are asking Excel to look down through the rows to find the correct item, and then right through the columns to find the correct region. Although VLOOKUP typically performs one-dimensional lookups, with a little hack we can convince it to do two-dimensional lookups. Ready? Me too!

## VLOOKUP

The VLOOKUP function has four arguments, and our little hack involves the third argument. Let’s take a quick look at the arguments.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

**Where**

**lookup_value**is the value we are trying to find**table_array**is the lookup range**col_index_num**is the column that has the value to return**[range_lookup]**tells Excel if we are doing a range lookup

Typically, the col_index_num argument is expressed as an integer value, such as 2 for the second column or 5 for the fifth column. However, if we use a function to determine the column number instead, then, VLOOKUP is able to look down for a matching item AND right for a matching region. The function we need here is MATCH.

## MATCH

The MATCH function returns the relative position of a list item. It has the following three arguments.

=MATCH(lookup_value, lookup_array, [match_type])

**Where**

**lookup_value**is the value we are trying to find**lookup_array**is the lookup range**[match_type]**will be zero for exact match

The idea of our little hack is to use the MATCH function to tell the VLOOKUP function which column stores the prices for the specified region.

Rather than enter an integer, such as 2, for the third VLOOKUP argument, we’ll use MATCH instead. For example, let’s revisit our price quote worksheet pictured below.

The formula in C9 that retrieves the correct price from the tbl_price table based on the item and region is shown below.

=VLOOKUP(C6,tbl_prices,MATCH(C7,tbl_prices[#Headers],0),0)

**Where**

**C6**is the item value we are trying to find**tbl_prices**is the lookup range, the prices table**MATCH(C7,tbl_prices[#Headers],0)**returns the column number of the specified region**Where****C7**is the region value we are trying to find**tbl_prices[#Headers]**is the structured reference for the table’s header row**0**means exact match

**0**means exact match

Now as we change the item and region values, our hacked VLOOKUP function will retrieve the correct price from the table…and that is a two-dimensional lookup with VLOOKUP!

Also note that since this is Excel, there are of course other options. For example, we could use the INDEX/MATCH combination instead, and if you are interested in that, check out the previous blog post referenced below.

## Additional resources

- Sample Excel file
- INDEX/MATCH with two dimensions blog post (see the Transpose with Formulas section)
- Drop-down blog post
- Tables posts

Jeff,

Now aren’t you slick!!! I was trying to figure out a solution for this very issue a couple of weeks ago. I never thought to use MATCH instead of an integer. This is great!!! thanks for the tip. Almost finished with Excel University Will start Vol 3 soon. Still practicing Vol 2, which is kicking my hiney. Sometimes my cerebellum logic can get wrapped around Excel logic. Fun to learn though. Excel University is a great little course. Made my job soooo much easier.

Retired CPA with small tax practice to keep me busy during winter months. I have used Excel for a number of years, but consider that I have only scratched the surface of what program can do.

Jeff

I need some help with a relatively simple problem but for weeks I simply have not been able to get someone in a expert potion with Excel to give me some guidance, can you help.

My problem is: (I will try and explain as best I can what I need to do)

I have a quote creation template that in cell C4 allows you to select (from a drop down) an item/product from a long list of kits, with set descriptions. (These are mirrored exactly in the price list)

What I want to have is cell (F4) show a price based on the drop down item selected in C4, and the price based on the hire period shown in cell (G4) – Daily / Weekly / Monthly. The 2 parts are on the same worksheet, I have copied and pasted below the headings and columns references.

Quote Template

(C4) (F4) (G4)

Description Quote No: Item # Qty Unit Price Rate Base Total

Compressor, 175cfm @ 100 psi HQ-00004 1 6 60 Monthly 360

Price List:

(K4) (P4) (T4) (X4)

Equipment Models Daily Weekly Monthly

Compressor, 175cfm @ 100 psi Compare 143 717 2150

Big question:

How can I get Cell F4 to show a price that is matched to an item/product selected in C4 from a drop down, and the price is based then on one of 3 variables (daily, weekly or monthly) chosen again form a drop down in cell G4. All prices are in a table co-located on the same worksheet for ease of formula building.

If anyone can help me solve this I would genuinely appreciate it as I need to complete this prior to taking the next step. I am open to any suggestions.

Regards

Scott Davison

Qatar

Scott,

Since the value you are trying to retrieve is a price, which is a numeric value, you can accomplish your goal with a SUMIFS function. I’ve written a blog post that shows how to perform a lookup on multiple conditions with SUMIFS here:

http://www.excel-university.com/vlookup-on-two-or-more-criteria-columns/

Hope it helps!

Thanks

Jeff

Jeff

Thanks for the prompt reply, I will read through the blog and see how I can apply it and create a formula that will give me the price in the cell.

I am sure it will work, thanks again any other advice directly related to the question I have will be gratefully received.

I will be signing up for the excel uni course level 1 very soon.

Regards

Scott

Excellent…best of luck…and look forward to seeing you in the LMS!

How to solve this

We have given mobiles no. In one sheet

And second sheet we have mobile no, state, city, name.

Using we vlookup

In sheet one mobile no, we find two columns at one time. State and city. Using one formula v lookup

Hey Sam,

Can you reword your question? I’m having trouble understanding it. Sorry for the misunderstanding!

Thanks,

Kurt LeBlanc

755737 322395Wohh exactly what I was searching for, regards for posting . 258759