MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP, HLOOKUP, OR COMBINATION?


Posted by Philip Smith on August 08, 2001 11:33 AM

Can you please advise me if it is possible to look up
a value in a 2 dimensional table & if so how as an
example a value may be chosen from the following table:


ThicknessÞ 3mm 4mm 5mm 6mm

Area(sq ft)
ß
0.25 £90.00 £94.20 £98.50 £102.70

0.50 £57.10 £61.40 £65.60 £69.80

0.75 £46.10 £50.30 £54.50 £58.70


N.B. The prices are per sq ft.

e.g. Suppose you wanted to pick the value &65.60 . How
would you do this?


Posted by faster on August 08, 2001 11:51 AM

The formula for return is:
=INDEX(A5:E8,MATCH(B2,A5:A8,0),MATCH(B1,A5:E5,0))


Area(sq ft) 5mm
ThicknessÞ 0.5
Return £65.60

ß 3mm 4mm 5mm 6mm
0.25 £90.00 £94.20 £98.50 £102.70
0.5 £57.10 £61.40 £65.60 £69.80
0.75 £46.10 £50.30 £54.50 £58.70


Posted by Aladin Akyurek on August 08, 2001 12:08 PM

Also with VLOOKUP+MATCH


=VLOOKUP(B1,A5:E8,MATCH(B2,A5:E8,0),0)

where B1 contains ThicknessÞ of interest and B2 Area(sq ft) of interest.

Aladin

: a value in a 2 dimensional table & if so how as an : example a value may be chosen from the following table

Posted by Aladin Akyurek on August 08, 2001 12:08 PM

Also with VLOOKUP+MATCH


=VLOOKUP(B1,A5:E8,MATCH(B2,A5:E8,0),0)

where B1 contains ThicknessÞ of interest and B2 Area(sq ft) of interest.

Aladin

: a value in a 2 dimensional table & if so how as an : example a value may be chosen from the following table