Returning a value from a list of static with two parameters, one of which uses a range

[booms]

Hi,

I’m trying to return a value from a large static table that includes a number of different parameters.

Feature 1 Min|	Feature 1 Max|	Feature 2	|Value
	0.1	A	1
0.1	0.2	A	2
0.2	0.3	A	3
	0.1	B	4
0.1	0.2	B	5
0.2	0.3	B	6

<tbody>
</tbody>

Here’s an example of the static table.

The data I’m trying to work with will give me a value for Feature 1 and for Feature 2. The value for feature 1 will lie in the range as specified in the static table, whereas Feature 2 doesn’t have that complexity.

e.g. For Feature 1 = 0.23 and Feature 2 = B I want to return ‘6’.

Any idea how to pick this up using a formula?

 

[wideboydixon]

If there's only going to be one match then perhaps:

Book1
	A	B	C	D	E	F	G
1	Feature 1 Min	Feature 1 Max	Feature 2	Value		Feature 1	0.23
2	0	0.1	A	1		Feature 2	B
3	0.1	0.2	A	2		Result	6
4	0.2	0.3	A	3
5	0	0.1	B	4
6	0.1	0.2	B	5
7	0.2	0.3	B	6
Sheet1

Cell Formulas
Range	Formula
G3	=INDEX($D1:$D$7,SUMPRODUCT(($A$1:$A$7<=$G$1)*($B$1:$B$7>$G$1)*($C$1:$C$7=$G$2)*ROW($D$1:$D$7)))

WBD

 

[Aladin Akyurek]

In G3 control+shif enter, not just enter:

=VLOOKUP(G1,CHOOSE({1,2},IF($C$2:$C$7=G2,$A$2:$A$7),$D$2:$D$7),2,1)

 

[booms]

Beautiful! Thanks very much. Works perfectly.