VLOOKUP and HLOOKUP Used Together???

[Verner]

I have a massive chart that gives a constant with Temp and Humidity as my variables. I have Temperature on the Y-Axis and Humidity on the X-Axis and for some reason can't figure out how to use VLOOKUP and HLOOKUP together so that EXCEL will pick out the constant on the chart for me. Is there a way to return such a value?

Example: how would I get the value for T=100, H=10% (.421)

T
150 .684 .701 .738 .800
100 .421 .455 .467 .582
50 .311 .352 .569 .255
0 .212 .232 .242 .252
-50 .112 .115 .118 .121
H 10 20 30 40 .....

 

[iken]

Using VLOOKUP.

Assume the temperature input is in cell A1, and the humidity is in cell B1

You need to associate a number to your humidty inputs, i.e.

10 = 2
20 = 3
30 = 4
etc

These depict what column you are looking up

Do a VLOOKUP for above humdity = VLOOKUP(B1,"range",2) (lets say this is in cell C1)

Now for your data

VLOOKUP(A1,"range",C1)

 

[GregLocock]

thread770-51434

discusses 2 dimensional lookup. If you want to interpolate then it is easier to use INDEX and MATCH



Cheers

Greg Locock

 

[MortenA]

This tread also explains it:

thread770-35981 r

Best regards

Morten

 

[lilliput1]

1 B C D E F G
2
3 T \ H 10 20 30 40
4 col = 1 2 3 4 5
5 -50 0.112 0.115 0.118 0.121
6 0 0.212 0.232 0.242 0.252
7 50 0.311 0.352 0.569 0.255
8 100 0.421 0.455 0.467 0.582
9 150 0.684 0.701 0.738 0.8
10
11 T H Value
12 50 40 0.255

Formula at Cell E12 =
Vlookup(C12,$C$5:$G$9,Hlookup(D12,$D$3:$G$4,2))

Input T at Cell C12
Input H at Cell H12
You have to arrange the T & H values in ascending order for Vlookup or Hlookup to work. Use the Data Sort feature because sometimes the Excel ascending order will not be what you expected. Note the limitation of the V & H lookups. It will not round off to the nearest no in the table. You can either interpolate or extrapolate to put in more values & also you can put in a rounding formula (replace D12 with formula Round(d12,-1)).

 

[iken]

Your data doesn't have to be in order for the use of these functions. Type "FALSE" afters last entry. This however will look for an exact match of input with values in list. Only good if you do not intend to interpolate.

 

[lilliput1]

Yes it has to be in ascending order otherwize the vlookup & hlookup will return the wrong value. Try changing the order of T &/or H values in the array & you will see what will happen.

 

[iken]

Your correct for data requiring sorting in your equation (both vlookup and hlookup in same function). However if these are in seperate cells as my first reply, then inserting a "false" statement at the end of both v & h lookups, then data doesn't need to be in any order.

 

[gunther]

The problem with VLOOKUP and HLOOKUP is that they do not check if the x and y value exist in your table. The only way to find the matching pair and the value is using INDEX(array,row_num,colum_num) in conjunction with match(lookup-_value,lookup_array,match_type).
match_type values are: -1, 0, or 1
If match_type is "0", MATCH finds the first value that is exactly equal to the lookup_value. The array can be in ANY order !!!! If value does not exist - error message will be shown

I.e. in your case the statement woulg read as follows:


A B C D E
T
1 150 .684 .701 .738 .800
2 100 .421 .455 .467 .582
3 50 .311 .352 .569 .255
4 0 .212 .232 .242 .252
5 -50 .112 .115 .118 .121
6 H 10 20 30 40 .....
7
8

Example: how would I get the value for T=100, H=10% (.421)

=INDEX(B1:E5,MATCH(100,A2:A5,0),MATCH(10,B6:E6,0)

or general form: =INDEX(B1:E5,MATCH("Cell with T value",A2:A5,0),MATCH("Cell with H value",B6:E6,0)

Gunther

 

[1raven]

You could try to use named ranges and intersection as a simple fix (could be a bit of set-up time if there are many rows and columns).

If you would rather have a little more set-up and a lot less complicated formulas, do this...

Select the row that has the humidity levels for temp 150 (all values starting with the temp value ending with the last humidity value).
Name that Row, t_150.
Do this for each row.
Now do the same for each humidity column (h_10, h_20, h_30, etc).

Now, in the cell where you want to show the intersection of the Row named t_150 and the Column named h_10, put this formula....
=t_150 h_10
the result would be .684.

The <space> is the intersection operator.

 

[d23]

All:

Being posted in August I would guess this is a little late, but to try and do both HLOOKUP and VLOOKUP wouldn’t “INDEX” be a better choice?

Just asking.

D23

 

[fred2002]

lilliput1 correctly said that T & H numbers have to be in ascending order for vlookup to work. So if spreadsheet now becomes something like this:

A B C D E
1
2 H
3 T 10 20 30 40
4 -50 0.112 0.115 0.118 0.121
5 0 0.212 0.232 0.242 0.252
6 50 0.311 0.352 0.569 0.255
7 100 0.421 0.455 0.467 0.582
8 150 0.684 0.701 0.738 0.800
9
10 T 100
11 H 10
12
13 Value 0.421

In cells C10 and C11 put in the values of T and H respectively you require. In cell C13 type the following formula:

=VLOOKUP(C10,A3:E8,MATCH(C11,B3:E3,0)+1,0)

and you should get the right answer.

Gunther's version adapted for the spreadsheet above is:

=INDEX(B4:E8,MATCH(C10,A4:A8,0),MATCH(C11,B3:E3,0))

Hope this helps