Return Cost Value Based on Two Other Cells.

[okcalla]

Formula Test.xlsx I need help getting Excel to return and expected value based on City and work code. On sheet 1 Employees can enter a work code in column E beginning in cell 24 currently cell f24 and g24 return a value, but pay no attention to cell M3. I need to modify the formula in cell G24 so that it returns a price based on the city in cell M3. M3 is a drop down that gets it data validation information from the 2nd sheet which also defines the work codes descriptions and cost per market. Row 25 has been manually calculated to return the expected value based on the cost code entered in E25 and the city entered in M3. I have tried combinations of vlookup's, nested if statements index and match but I seem to be missing something. Any help is greatly appreciated.

[pjwhitfield]

Try

Please Login or Register  to view this content.

which basically does the vlookup but uses a match on your Costs table to find which column to return for your required city.

EDIT John's slightly different version below is the right one to use, I always forget to lock the ranges with $

[JohnTopley]

Try

=IF($E24="",0,VLOOKUP($E24,'Work Costs'!$A$3:$G$123,MATCH($M$3,'Work Costs'!$A$2:$G$2,0),0))

[oeldere]

With index / match / match

K5 =INDEX('Work Costs'!$A$2:$G$15,MATCH($K$3,'Work Costs'!$A$2:$A$15,0),MATCH($M$3,'Work Costs'!$A$2:$G$2,0))

See the attached file.

[okcalla]

Try

=IF($E24="",0,VLOOKUP($E24,'Work Costs'!$A$3:$G$123,MATCH($M$3,'Work Costs'!$A$2:$G$2,0),0))

Bingo, This works perfectly.