×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VLOOKUP and HLOOKUP Used Together???

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 .....

RE: VLOOKUP and HLOOKUP Used Together???

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)

RE: VLOOKUP and HLOOKUP Used Together???

Thread770-51434

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???

This tread also explains it:

Thread770-35981 r

Best regards

Morten

RE: VLOOKUP and HLOOKUP Used Together???

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)).

RE: VLOOKUP and HLOOKUP Used Together???

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.

RE: VLOOKUP and HLOOKUP Used Together???

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.

RE: VLOOKUP and HLOOKUP Used Together???

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.

RE: VLOOKUP and HLOOKUP Used Together???

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

RE: VLOOKUP and HLOOKUP Used Together???

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.

RE: VLOOKUP and HLOOKUP Used Together???

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

RE: VLOOKUP and HLOOKUP Used Together???

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources