Help needed !! Look Up Table function ??

  • Thread starter Thread starter Big Bad Nige
  • Start date Start date
B

Big Bad Nige

Can anyone tell me how to create a table or worksheet of information
comprising a grid of prices, I then want to be able to enter 2 values into a
form & have excel look-up the relevant price from the table.

Height of product along the Y axis, Width of product along the X axis, with
prices of the product for each variable.

100 150 200 250 (W)
100 50 51 52 53
150 55 56 57 58
200 60 61 62 63
250 65 66 67 68
(H)

EG, I want to be able to enter the Width & Height info into a form, say 150W
x 200H & excel returns the correct price, in this case £61.....
 
Hi,

With your table in A1 - E20 try this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

With the 2 lookup values in F1 & G1.

Mike
 
One way is index/match
Assume table as posted is in A1:E5
In G2 is the input for "H", eg: 150
In H2 is the input for "W" eg: 200
Then in I2: =INDEX(B2:E5,MATCH(H2,A2:A5,0),MATCH(G2,B1:E1,0))
will return the required intersection value, ie: 61
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
Thank you, very helpful.

Mike H said:
Hi,

With your table in A1 - E20 try this

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

With the 2 lookup values in F1 & G1.

Mike
Click to expand...
 
Thank you, very helpful.

Max said:
One way is index/match
Assume table as posted is in A1:E5
In G2 is the input for "H", eg: 150
In H2 is the input for "W" eg: 200
Then in I2: =INDEX(B2:E5,MATCH(H2,A2:A5,0),MATCH(G2,B1:E1,0))
will return the required intersection value, ie: 61
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
Click to expand...
 
Back
Top