Lookup

[Duritz]

If I have a range like this in columns A-C -

A B C
1 2 34.6
1 3 5.3
1 4 19.7
1 5 8.6
1 6 17.3
1 7 7.6
1 8 8.1
2 3 0
2 4 138
2 5 0
2 6 0
2 7 138
2 8 138
3 4 46.1
3 5 15.3
3 6 69.2
3 7 9.8
3 8 12.5
4 5 138
4 6 138
4 7 69.2
4 8 0


And in range d2:e2 I have this

D E
3 7

How do I lookup that 3 7 and return the value corresponding, ie 9.8?

 

[yee388]

Here's one solution:

Book1
	A	B	C	D	E	F
1	1	2	34.6
2	1	3	5.3	3	7	9.8
3	1	4	19.7
4	1	5	8.6
5	1	6	17.3
6	1	7	7.6
7	1	8	8.1
8	2	3	0
9	2	4	138
10	2	5	0
11	2	6	0
12	2	7	138
13	2	8	138
14	3	4	46.1
15	3	5	15.3
16	3	6	69.2
17	3	7	9.8
18	3	8	12.5
19	4	5	138
20	4	6	138
21	4	7	69.2
22	4	8	0
Sheet1

 

[Aladin Akyurek]

A common idiom, which needs to be confirmed with control+shift+enter...

=INDEX($C$2:$C$23,MATCH(1,IF($A$2:$A$23=D2,IF($B$2:$B$23=E2,1)),0))

where D2 houses 3 and E2 7.

Also, possibly because the (sub)ranges are in ascending order...

=LOOKUP(E2,OFFSET($B$2,MATCH(D2,$A$2:$A$23,0)-1,0,MATCH(D2,$A$2:$A$23,1)-MATCH(D2,$A$2:$A$23,0),2))