return value based on lookup of two criteria

[obiwann]

How can I add to this formula to find the closest result in my temp. column? If I enter an exact temperature it
will return the correct result. Ex: -17.56 = 1.11759

My formula is: =INDEX(C2:C8, MATCH(G2&G3, A2:A8&B2:B8, 0))

Any help would be appreciated.

[TABLE="width: 350"]
<tbody>[TR]
[TD="width: 64"]

density​

[/TD]
[TD="width: 64"]

temp.​

[/TD]
[TD="width: 64"]

CTL​

[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 155, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

45​

[/TD]
[TD="width: 64, bgcolor: transparent"]

-17.78​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.1182​

[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 155"] Density
[/TD]
[TD="width: 103, bgcolor: transparent"]

45​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

45​

[/TD]
[TD="width: 64, bgcolor: transparent"]

-17.72​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.11813​

[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 155"] Temp.
[/TD]
[TD="width: 103, bgcolor: transparent"]

-17.6​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

45​

[/TD]
[TD="width: 64, bgcolor: transparent"]

-17.67​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.11795​

[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 155"] CTL
[/TD]
[TD="width: 103, bgcolor: transparent"]

#N/A​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

45​

[/TD]
[TD="width: 64, bgcolor: transparent"]

-17.61​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.11777​

[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 155, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

45​

[/TD]
[TD="width: 64, bgcolor: transparent"]

-17.56​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.11759​

[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 155, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

45​

[/TD]
[TD="width: 64, bgcolor: transparent"]

-17.50​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.11741​

[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 155, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

45​

[/TD]
[TD="width: 64, bgcolor: transparent"]

-17.44​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.11723​

[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 155, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F
1	density	temp.	CTL
2	45	-17.78	1.1182		Density	45
3	45	-17.72	1.11813		Temp.	-17.6
4	45	-17.67	1.11795		CTL	1.11777
5	45	-17.61	1.11777
6	45	-17.56	1.11759
7	45	-17.5	1.11741
8	45	-17.44	1.11723
Sheet

 

[obiwann]

Wow!
Works perfectly, thank you very much.

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Eric W]

If you want something a little fancier, this non-array formula will find the 2 bracketing values and perform linear interpolation between them:

=FORECAST(F3,OFFSET($C$2,LOOKUP(2,1/(($A$2:$A$8=F2)*($B$2:$B$8<=F3)),ROW($C$2:$C$8)-ROW($C$2)),0,2),OFFSET($B$2,LOOKUP(2,1/(($A$2:$A$8=F2)*($B$2:$B$8<=F3)),ROW($C$2:$C$8)-ROW($C$2)),0,2))