Using HLOOKUP & VLOOKUP 2

[jetboat]

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

 

[swertel]

There you go. Answered in column C.

--Scott

http://wertel.eng.pro

 

[dik]

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.

 

[swertel]

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