Vlookup based on 2 values

TSing3

Board Regular
Joined
Jan 7, 2015
Messages
50
I need help with a formula to return a value based on 2 criteria. I have a list of part numbers in coulmn A. My column D:I headers are 55, 56, 57, 58, 59, 60. Under each are a list of prices. So, each part number in column A has a different price under each column 55, 56, 57 , etc. So, the part number and all of it's prices are in the same row.

On another sheet I am listing a part number and need to return the price based on what I select from a drop down box......55, 56, 57, etc.

How can I do this?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
How about, something like
Excel Formula:
=INDEX(Sheet1!D2:I100,MATCH(A2,Sheet1!A2:A100,0),MATCH(B2,Sheet1!D1:I1,0))
 

TSing3

Board Regular
Joined
Jan 7, 2015
Messages
50
That did not work. Maybe I did it wrong or maybe I can try to explain it better. I'm entering the part number on a sheet in B18. I'm selecting the other value (55, 57, 58, etc.) from a drop down in G9.

I need those 2 values to return the price. All of the part numbers are in column A on another sheet and the prices are on the same row, under columns D:I D1:I1 have the 55, 57, 58, etc.) prices start at D2:I2 and down.
 

TSing3

Board Regular
Joined
Jan 7, 2015
Messages
50

ADVERTISEMENT

I'm getting a Spill error
 

ManUBlueJay

Active Member
Joined
Aug 30, 2012
Messages
250
Office Version
  1. 2016
Platform
  1. Windows
This Formula should Work. If your Data is on Sheet1. Change if Necessary.
Assuming Your Part Number is in Sheet2, A1, and the drop down is in B1.
=OFFSET(Sheet1!A1,MATCH(Sheet2!A1,Sheet1!A2:A100,0),MATCH(B1,Sheet1!$D$1:$I$1,0)+2)
 

TSing3

Board Regular
Joined
Jan 7, 2015
Messages
50

ADVERTISEMENT

That did not work either. Question....I'm selecting D1:I1, but not selecting the prices underneath. How does the formula know to look for the prices? What is the +2 for?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
What is the name of the sheet with the prices?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
In that case how about
Excel Formula:
=INDEX(SAP!D2:I100,MATCH(B18,SAP!A2:A100,0),MATCH(G9,SAP!D1:I1,0))
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,102
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top