×
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

Using HLOOKUP & VLOOKUP
2

Using HLOOKUP & VLOOKUP

Using HLOOKUP & VLOOKUP

(OP)
I have attached the excel file in question. Basically I have two input values which are the yellow highlighted cells and would like to use formulas to calculate needed value (ie. ks) The cells highlighted in red are the cells I am trying to enter an equation to look up values in the table. I believe I would need to use a combination of the hlookup and vlookup but haven't had any luck. What I am trying to do is put the value from the table in the red cell using both input values (yellow highlighted cells). I have manually placed the corresponding values from the table in the red cells  for clarity. In short all I trying to do is put an equation in the red cells to accomplish the following. Take the Z value from Cell A9 and find the corresponding value in column E. From there, take the value from cell B5 and find the corresponding row F6-H6. Finally cross the row chosen (F6-H6) with the value from A9. If anyone could explain or show me an example equation to use that would be great!   

RE: Using HLOOKUP & VLOOKUP

Thanks Swertel for the different approach.

I've always solved this type of problem by using a 0 value for height, the same as the 15' height and by using a separate column on the righthand side that is offset by a row. for example the row value for 0 on the LHS would have a 15 on the RHS.

RE: Using HLOOKUP & VLOOKUP

Glad I could help.  I should clarify the "+1" term I have in there.

If E3:E5 wasn't a merged cell, I could set my MATCH() range to be E5:H5 and that would be that.  But, since I had to offset my range by 1 to F5:H5 due to the merged cell, I had to add a "+1" value to get the column number to align with the VLOOKUP() matrix.

In other words, the 2nd column of the MATCH() range is actually the 3rd column of the VLOOKUP() range, i.e. +1.

--Scott
http://wertel.eng.pro

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