# Vlookup based on 2 values

#### TSing3

##### Board Regular
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?

### 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
Excel Formula:
``=INDEX(Sheet1!D2:I100,MATCH(A2,Sheet1!A2:A100,0),MATCH(B2,Sheet1!D1:I1,0))``

#### TSing3

##### Board Regular
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.

Watch this video

#### TSing3

##### Board Regular

I'm getting a Spill error

#### ManUBlueJay

##### Active Member
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

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
What is the name of the sheet with the prices?

SAP

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=INDEX(SAP!D2:I100,MATCH(B18,SAP!A2:A100,0),MATCH(G9,SAP!D1:I1,0))``

Replies
5
Views
178
Replies
3
Views
54
Replies
13
Views
195
Replies
1
Views
98
Replies
0
Views
239

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.

### Which adblocker are you using?

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

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