Vlookup based on 2 values

[TSing3]

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?

 

[Fluff]

How about, something like

=INDEX(Sheet1!D2:I100,MATCH(A2,Sheet1!A2:A100,0),MATCH(B2,Sheet1!D1:I1,0))

 

[TSing3]

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.

 

[jocote46]

Watch this video

 

[TSing3]

I'm getting a Spill error

 

[ManUBlueJay]

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]

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]

What is the name of the sheet with the prices?

 

[TSing3]

SAP

 

[Fluff]

In that case how about

=INDEX(SAP!D2:I100,MATCH(B18,SAP!A2:A100,0),MATCH(G9,SAP!D1:I1,0))