Help needed !! Look Up Table function ??
 

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

Help needed !! Look Up Table function ??
 

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

"Big Bad Nige" wrote:

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

Help needed !! Look Up Table function ??
 

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
---
"Big Bad Nige" wrote:
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.....

Help needed !! Look Up Table function ??
 

Thank you, very helpful.

"Mike H" wrote:

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

"Big Bad Nige" wrote:

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

Help needed !! Look Up Table function ??
 

Thank you, very helpful.

"Max" wrote:

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
---
"Big Bad Nige" wrote:
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.....

Help needed !! Look Up Table function ??
 

your welcome


"Big Bad Nige" wrote:

Thank you, very helpful.

"Mike H" wrote:

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

"Big Bad Nige" wrote:

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

Help needed !! Look Up Table function ??
 

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Big Bad Nige" wrote in message
...
Thank you, very helpful.