combining HLOOKUP AND VLOOKUP to find solutions in a matrix using 2 variables

This was a snap in Lotus. Unfortunately, the VLOOKUP and HLOOKUP formulas don't translate to Excel on conversion.

I need to estimate the weight of reinforcing steel in concrete based on 7 different sizes of rebar and spacing of the bar between 1" and 24".

I have created a matrix(lookup table) with the bar spacing listed in decimal equivilents in feet on the top row.Inthe row below, I have numbered the columns from 1 to 24. the next row is blank. Along the left side of the matrix, I have listed the bar sizes 3 thru 10. Within the table I have listed all the bar weights/SF of concrete. IE if the slab has 1 mat of #5 rebar spaced 6" oc. If you look in row 7, under column 6, the weight of weight of the bar in that sf of concrete is found. In the part of the worksheet where the caculations are performed set up as follows:
Rebar Length Width SF Bar Size Spacing in feet Column Ref
Top mat 5 0.5 6 #N/A
Where B142 is lenght
C142 is width
D142 is =a142*B142
E142 is the bar size
F142 is the spacing of the bar in decimal equivilents of FT in this case .5
G142 is the is the formula =HLOOKUP(F142,reference,2,TRUE)
H 142 is the formula =VLOOKUP(E142,convert3,G142)

The returned value is 2.472, which is the value for #5 rebar 5" on center,not 6" OC desired

The range "reference" includes the cells at the top of the lookup table U2 thru AR3

The range contained in the "convert3" is cells T2 thru AR12.

I have tried tweaking the rows and column included in the ranges to no avail.

What am I doing wrong?

My point was

G142: =HLOOKUP(F142,Reference,2,TRUE)

returns 6 given 6 appears in 2nd row of your Reference range for 0.5 however in reality this column (Z) is in fact column 7 of your vlookup range convert3 which runs from T:AR therefore you must adjust for the missing column by either

a) incrementing the result of the HLOOKUP in the col_index_num variable, ie:

H142: =VLOOKUP(E142,convert3,G142+1)

or

b) by altering your row 3 values such that each value represents it's real position in the VLOOKUP table, ie U3 becomes 2 not 1, V3 becomes 3 not 2 and so on and so forth

Alternatively dispense with Reference etc altogether...

H142: =VLOOKUP($E142,convert3,MATCH($F142,$T$2:$AR$2,0))

You actually state the problem yourself...

The range "reference" includes the cells at the top of the lookup table U2 thru AR3
The range contained in the "convert3" is cells T2 thru AR12.

So you must adjust the column reference as used in the VLOOKUP to account for the fact that the VLOOKUP range has one additional column (T) than the Reference range (ie column 6 in Reference = column 7 in convert3).