VLOOKUP and HLOOKUP Used Together???
VLOOKUP and HLOOKUP Used Together???
(OP)
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 .....
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 .....





RE: VLOOKUP and HLOOKUP Used Together???
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)
RE: VLOOKUP and HLOOKUP Used Together???
discusses 2 dimensional lookup. If you want to interpolate then it is easier to use INDEX and MATCH
Cheers
Greg Locock
RE: VLOOKUP and HLOOKUP Used Together???
Thread770-35981 r
Best regards
Morten
RE: VLOOKUP and HLOOKUP Used Together???
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)).
RE: VLOOKUP and HLOOKUP Used Together???
RE: VLOOKUP and HLOOKUP Used Together???
RE: VLOOKUP and HLOOKUP Used Together???
RE: VLOOKUP and HLOOKUP Used Together???
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
RE: VLOOKUP and HLOOKUP Used Together???
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.
RE: VLOOKUP and HLOOKUP Used Together???
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
RE: VLOOKUP and HLOOKUP Used Together???
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