Help needed !! Look Up Table function ??

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.....
 
M

Mike H

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
 
M

Max

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
 
B

Big Bad Nige

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
 
B

Big Bad Nige

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top