is there a way to combine a vlookup and hlookup - i.e., search with two terms

[msampson]

I have a table of values where column A starting in row 2 is parameter one which increases as the rows go down. Columns B-F in row 1 are increasing values of parameter two. Filling in the rest of the table are the results obtained with an input from parameter one and parameter two. How can I draft a lookup with two inputs, one vertical and one horizontal?


A. B. C. D. E. F.
<100 101-120. 121-140 141-160 >160
<41 3.5 3.3 3.1 3.0 2.7
42-65 3.8 3.7 3.5 3.3 3.1
66-90 4.1 3.9 3.6 3.4 3.3
91-120 4.5 4.4 4.1 3.9 3.6
121-300 4.8 4.6 4.3 4.2 3.9
301-1000 5.4 5.1 4.8 4.5 4.3
>1000 7.3 6.9 6.8 6.6. 6.5

So I want to enter, say parameter one is 72 and parameter two is 122 and get 3.6 returned.
Or enter 32 and 76 and get 3.5

thanks, Maureen

 

[Eric W]

If you rearrange your table a bit, so the ranges on the horizontal and vertical look-up ranges represent the low boundary, then you can do this:

	A	B	C	D	E	F	G	H	I	J	K	L
Parameter 1	Parameter 2	Result

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]3.3[/TD]
[TD="align: right"]3.1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]3.7[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]3.3[/TD]
[TD="align: right"]3.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]4.1[/TD]
[TD="align: right"]3.9[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]3.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.4[/TD]
[TD="align: right"]4.1[/TD]
[TD="align: right"]3.9[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.3[/TD]
[TD="align: right"]4.2[/TD]
[TD="align: right"]3.9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]301[/TD]
[TD="align: right"]5.4[/TD]
[TD="align: right"]5.1[/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]7.3[/TD]
[TD="align: right"]6.9[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]6.6[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=INDEX(B2:F8,MATCH(I2,A2:A8),MATCH(J2,B1:F1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td]

<100​

[/td][td]

101-120​

[/td][td]

121-140​

[/td][td]

141-160​

[/td][td]

>160​

[/td][td][/td][td]

Param1​

[/td][td]

Param2​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][td="bgcolor:#DCE6F1"]

0​

[/td][td="bgcolor:#DCE6F1"]

101​

[/td][td="bgcolor:#DCE6F1"]

121​

[/td][td="bgcolor:#DCE6F1"]

141​

[/td][td="bgcolor:#DCE6F1"]

161​

[/td][td][/td][td]

72​

[/td][td]

122​

[/td][td]

3,6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

<41​

[/td][td="bgcolor:#DCE6F1"]

0​

[/td][td]

3,5​

[/td][td]

3,3​

[/td][td]

3,1​

[/td][td]

3,0​

[/td][td]

2,7​

[/td][td][/td][td]

32​

[/td][td]

76​

[/td][td]

3,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

42-65​

[/td][td="bgcolor:#DCE6F1"]

42​

[/td][td]

3,8​

[/td][td]

3,7​

[/td][td]

3,5​

[/td][td]

3,3​

[/td][td]

3,1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

66-90​

[/td][td="bgcolor:#DCE6F1"]

66​

[/td][td]

4,1​

[/td][td]

3,9​

[/td][td]

3,6​

[/td][td]

3,4​

[/td][td]

3,3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

91-120​

[/td][td="bgcolor:#DCE6F1"]

91​

[/td][td]

4,5​

[/td][td]

4,4​

[/td][td]

4,1​

[/td][td]

3,9​

[/td][td]

3,6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

121-300​

[/td][td="bgcolor:#DCE6F1"]

121​

[/td][td]

4,8​

[/td][td]

4,6​

[/td][td]

4,3​

[/td][td]

4,2​

[/td][td]

3,9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

301-1000​

[/td][td="bgcolor:#DCE6F1"]

301​

[/td][td]

5,4​

[/td][td]

5,1​

[/td][td]

4,8​

[/td][td]

4,5​

[/td][td]

4,3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

>1000​

[/td][td="bgcolor:#DCE6F1"]

1001​

[/td][td]

7,3​

[/td][td]

6,9​

[/td][td]

6,8​

[/td][td]

6,6​

[/td][td]

6,5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Create the two ranges in blue, B3:B9 and C2:G2, that contain the lower limits of each track

Parameters in columns I:J

Formula in K2 copied down
=INDEX($C$3:$G$9,MATCH(I2,$B$3:$B$9),MATCH(J2,$C$2:$G$2))

Hope this helps

M.

 

[msampson]

AWESOME! thanks all

 

[kvsrinivasamurthy]

Post deleted.

 

[Peter_SSs]

If you did just want to keep your ranges at the left and top, provided you could modify the first and last ones a little as shown, you could perhaps also use this.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		0-100	101-120	121-140	141-160	161-			Param 1	Param 2	Result
2	0-41	3.5	3.3	3.1	3	2.7			72	122	3.6
3	42-65	3.8	3.7	3.5	3.3	3.1			32	76	3.5
4	66-90	4.1	3.9	3.6	3.4	3.3			1500	170	6.5
5	91-120	4.5	4.4	4.1	3.9	3.6
6	121-300	4.8	4.6	4.3	4.2	3.9
7	301-1000	5.4	5.1	4.8	4.5	4.3
8	1001-	7.3	6.9	6.8	6.6	6.5
Lookup