Return a value:index/match against multiple criteria on another sheet

[Southfish]

Hi all,

I’m using Excel 2003 and have order entry sheet (Sheet1) where an order is entered with a date, part name, quality grade, supply market and I’m trying to return a price from another sheet (Sheet2) which is an array with matching fields. The price on Sheet2 changes periodically and for each Part I’m trying to pull in the price from Sheet2 automatically into the Price field on Sheet1 based upon matching it to the most recent date of price change and by matching it to the other criteria part name, quality grade, supply market.

For example, in the attached workbook (Sample_Price) re: Sheet1, Date=3/26/2012, Part=Bolt, Quality Grade=3, Supply Mkt=Export, the price returned from Sheet2 would be based upon the Sheet2 Date=3/24/2012, and price returned would be 1.60.

All my attempts thus far using IF and INDEX/MATCH functions have been unsuccessful as the best I was able to do is have #N/A returned in the Price field on Sheet1.

I’m almost bald already so I’m running out of hair to pull out. Can anyone help…with the Excel issue not the balding?

Thanks much!

[icestationzbra]

the formulaic result for your example (quote above) is different from the one expected by you because GRADE in Sheet1 is 1 and not 3. check if that is correct and revert here if it is not, along with an explanation.

EDIT: file with updated formula - use this instead of the file uploaded earlier.

[Southfish]

Worked Perfectly!

Thanks so much!

[Southfish]

Yes I had figured that out as it did not pull a price created on the same date.

You're very meticulous which I gues you have to be when dealing with apps.

Thanks again for your time spent in resolving this issue as itis a big deal for me in allowing me to pass through this gate on my project.