Help with a Complex VLOOKUP 1

[DoctorV3774]

I have a little table in Excel in cells A1:F5. In cell E9 to the right of my Formula Label I need a formula to evaluate the value in E7 (in this case A3) and evaluate the value in E8 (in this case 115) and read through the column headings to return a result of 3 PART. How can I write a VLOOKUP formual or INDEX(MATCH formula to do this? Thanks

1 PART 2 PART 3 PART 4 PART 5 PART
A3 100 110 120 130 140
A4 200 210 220 230 240
A5 300 310 320 330 340
A6 400 410 420 430 440

Code A3
Value 115
Formula

 

[SkipVought]

Hi,

You may have to adjust the values in your table, but try this.
[tt]
=INDEX(B1:F1,1,MATCH(E8,OFFSET(B1,MATCH(E7,A2:A5,0),0,1,5),1))[/tt]

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[DoctorV3774]

Skip,
Thanks for your response. This is VERY close. It seems to be rounding down in the lookup rather than rounding up. example A3 with 106 is giving me 1 PART when I thought it should give me 2 PART because it is closer to that value. But is that the way INDEX Match evaluates values? Thanks again

 

[SkipVought]

You'll just have to adjust your table values for this to work.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]