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

msampson

Board Regular
Joined
Mar 9, 2004
Messages
129
Office Version
  1. 365
Platform
  1. MacOS
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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>
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top