Multi-criteria lookup while assigning single number to a numerical range

[cooker6583]

Hello all - trying to create multi-criteria lookup with <= and > logic. Think it's some sort of array index/match combo but cannot find any examples and haven't been able to piece it together.

Given a single number, I need to assign it to the appropriate range while also accounting for Name. I want to return the correct value depending on the targeted Factor. Any thoughts?

Here is an example of my data set structure:

[TABLE="width: 436"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Factor 1[/TD]
[TD]Factor 2[/TD]
[TD]Factor 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item 1[/TD]
[TD]0[/TD]
[TD]1500[/TD]
[TD]A[/TD]
[TD]12[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item 1[/TD]
[TD]1500[/TD]
[TD]3000[/TD]
[TD]B[/TD]
[TD]24[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item 2[/TD]
[TD]0[/TD]
[TD]1500[/TD]
[TD]B[/TD]
[TD]24[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Item 2[/TD]
[TD]1500[/TD]
[TD]3000[/TD]
[TD]C[/TD]
[TD]24[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Item 2[/TD]
[TD]3000[/TD]
[TD]5000[/TD]
[TD]D[/TD]
[TD]24[/TD]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]


As an example, if given Item 1 and 1600, result for a Factor 1 lookup is B because 1600 falls into the range 1500-3000.

I'm a non-VBA user...thanks in advance for the help!

 

[Aladin Akyurek]

E1: Item 1, F1: 1600, G1: Factor 1

In H1 control+shift+enter, not just enter:

=INDEX($D$2:$F$6,MATCH($E1,IF($F1>=$B$2:$B$6,IF($F1<=$C$2:$C$6,$A$2:$A$6)),0),MATCH(G$1,$D$1:$F$1,0))