INDEX MATCH Query - Return A Value From A Range Based On Two Criteria

[jonsharman]

Hi All,

I am trying to return a monetary value using INDEX MATCH based on two criteria that will search through a range of data - example of the sheet below:

DATA		LOOKUP		RETURN
CO2	P	D		116	P		£20.00
0 - 99	£10.00	£0.00
100 - 109	£15.00	£15.00
110 - 119	£20.00	£20.00
120 - 129	£115.00	£90.00
130 - 139	£175.00	£150.00

<tbody>
</tbody>

I have named the ranges CO2, P and D and in the above example I want to return the figure £20.00 based on looking for the value 116 which exists between 110 - 119 in the CO2 range and the P range. Likewise if I was to search for 135 and and D I would want to return £150.00

I have tried using TRUE to search for a near match but I get a #N/A error and I just cannot see what I am doing wrong.

Any help would be appreciated.

Thanks

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C	D	E	F	G
1	DATA				LOOKUP		RETURN
2	CO2	P	D		116	P	£20.00
3	0	£10.00	£0.00
4	100	£15.00	£15.00
5	110	£20.00	£20.00
6	120	£115.00	£90.00
7	130	£175.00	£150.00
Sheet1

Cell Formulas
Range	Formula
G2	=LOOKUP(E2,A3:A7,INDEX(B3:C7,0,MATCH(F2,B2:C2,FALSE)))

 

[Mike Szczesny]

Here is a suggestion. Use the index with the match functions together.
=index(B3:C7,match(E2,A3:A7,1),match(F2,B2:C2,False)). Using 1 for the match type will look for 120 (cell A6), but will look for the next lower number 110 (cell A5). This will select the value in B5.