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.

[TABLE="width: 296"]
<tbody>[TR]
[TD]Feature 1 Min|
[/TD]
[TD]Feature 1 Max|
[/TD]
[TD]Feature 2
[/TD]
[TD]|Value
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0.1
[/TD]
[TD]A
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]0.1
[/TD]
[TD]0.2
[/TD]
[TD]A
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]0.2
[/TD]
[TD]0.3
[/TD]
[TD]A
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0.1
[/TD]
[TD]B
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]0.1
[/TD]
[TD]0.2
[/TD]
[TD]B
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]0.2
[/TD]
[TD]0.3
[/TD]
[TD]B
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

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.