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

msampson

Board Regular
Joined
Mar 9, 2004
Messages
94
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,197
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:

ABCDEFGHIJKL
10101121141161Parameter 1Parameter 2Result
203.53.33.132.7721223.6
3423.83.73.53.33.1
4664.13.93.63.43.3
5914.54.44.13.93.6
61214.84.64.34.23.9
73015.45.14.84.54.3
810017.36.96.86.66.5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
K2=INDEX(B2:F8,MATCH(I2,A2:A8),MATCH(J2,B1:F1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,355
Maybe something like this


A
B
C
D
E
F
G
H
I
J
K
1
<100​
101-120​
121-140​
141-160​
>160​
Param1​
Param2​
Result​
2
0​
101​
121​
141​
161​
72​
122​
3,6​
3
<41​
0​
3,5​
3,3​
3,1​
3,0​
2,7​
32​
76​
3,5​
4
42-65​
42​
3,8​
3,7​
3,5​
3,3​
3,1​
5
66-90​
66​
4,1​
3,9​
3,6​
3,4​
3,3​
6
91-120​
91​
4,5​
4,4​
4,1​
3,9​
3,6​
7
121-300​
121​
4,8​
4,6​
4,3​
4,2​
3,9​
8
301-1000​
301​
5,4​
5,1​
4,8​
4,5​
4,3​
9
>1000​
1001​
7,3​
6,9​
6,8​
6,6​
6,5​
10

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,763
Office Version
365
Platform
Windows
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
ABCDEFGHIJK
10-100101-120121-140141-160161-Param 1Param 2Result
20-413.53.33.132.7721223.6
342-653.83.73.53.33.132763.5
466-904.13.93.63.43.315001706.5
591-1204.54.44.13.93.6
6121-3004.84.64.34.23.9
7301-10005.45.14.84.54.3
81001-7.36.96.86.66.5
Lookup
 
Last edited:

Forum statistics

Threads
1,086,220
Messages
5,388,550
Members
402,120
Latest member
dmitrevski

Some videos you may like

This Week's Hot Topics

Top