Vlookup Table

[slovenc0417]

I have a workbook in which I will be calculating the cost of a foundation wall for a specific building. "Worksheet #1" has a table of costs per lineal foot based on the size of a concrete foundation wall. Column A lists "heights" from 1'0 to 16'0, while Row 1 lists "thicknesses" from 8" to 14".

"Worksheet #2" will have columns with a breakdown of any combination of wall height and thickness that pertains to that specific building. For example, cell A1 will have a value of "8"" and cell B1 will have a value of "9'0". I would like cell C1 to automatically show the cost per lineal foot of that wall based on referencing the table in "Worksheet #1".

How can I make Excel search Column A for the "9'0" then then search Row 1 for "8"" thus choosing the intersecting value of the row and column and entering into cell C1? That would then tell me the cost per lineal foot of an 8"x9'0 concrete wall.

I am familar with VLOOKUP and HLOOKUP, but not with how i can make them work together.

Any help would be greatly appreciated!!!

Thanks!!!

[corinereyes]

Hi,

Can you post a simple example in an excel file?

[Paul]

Hi slovenc,

I think this might help you out. See the example worksheet attached.

Here's the formula I used on Worksheet #2, column C:

Please Login or Register  to view this content.

[slovenc0417]

Hi slovenc,

I think this might help you out. See the example worksheet attached.

Here's the formula I used on Worksheet #2, column C:

Please Login or Register  to view this content.

Hi pjoaquin,

I tried deciphering your code and kind of understand it. However, I am not getting it to work out yet. I weent back and looked at your example and noticed that the value in "C3, Worksheet #2" is incorrect. It seems that this worksheet only works when a thickness of 10" or higher is entered. Anything in the 8" and 9" columns is not working.

I have attached an example of my spreadsheet to help you help me... I am hoping this will help.

Thanks!!!

[slovenc0417]

Hi pjoaquin,

I tried deciphering your code and kind of understand it. However, I am not getting it to work out yet. I weent back and looked at your example and noticed that the value in "C3, Worksheet #2" is incorrect. It seems that this worksheet only works when a thickness of 10" or higher is entered. Anything in the 8" and 9" columns is not working.

I have attached an example of my spreadsheet to help you help me... I am hoping this will help.

Thanks!!!

I guess sending the attachment would help!!!

[slovenc0417]

Hi,

Can you post a simple example in an excel file?

I hope this helps.

Thanks!!!

[Dean England]

slovenc0417,

Take a look at the attachment and see if it works for you. I've altered it slightly. Hopefully it's what you're looking for.

Dean

[WinteE]

On my site you'll find a VHLOOKUP() function, which is an UDF.
With this function you can search both vertical and horizontal, just you are looking for.

Erik

[corinereyes]

Hi slovenc0417,

Try this formula:

Please Login or Register  to view this content.

See your workbook with the above formula.