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:

	A	B	C	D	E	F
1	Name	Min	Max	Factor 1	Factor 2	Factor 3
2	Item 1	0	1500	A	12	Green
3	Item 1	1500	3000	B	24	Blue
4	Item 2	0	1500	B	24	Yellow
5	Item 2	1500	3000	C	24	Purple
6	Item 2	3000	5000	D	24	Blue

<tbody>
</tbody>

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))