I have a table containing prices of a product with the width across the
columns and length down the rows looking something like this;

25 30 35 40
25 4.60 5.20 6.35 7.10
30 5.15 6.20 7.85 8.30
35 6.15 7.50 8.30 9.20

What I am looking for is for the user to enter values in 2 cells (width and
length), and have the price returned for the product in the price cell. eg;
if the user enters width 32 and length 26 it would return 7.85 in the price
cell (it must always round up not down)

I hope you understand what I want. Thanks in anticipation.

Assuming your table is in A1:E4, and the lookup length is in H1, and the
lookup width is in H2, try this

=INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LesLdh" wrote in message
...
I have a table containing prices of a product with the width across the
columns and length down the rows looking something like this;

25 30 35 40
25 4.60 5.20 6.35 7.10
30 5.15 6.20 7.85 8.30
35 6.15 7.50 8.30 9.20

What I am looking for is for the user to enter values in 2 cells (width
and
length), and have the price returned for the product in the price cell.
eg;
if the user enters width 32 and length 26 it would return 7.85 in the
price
cell (it must always round up not down)

I hope you understand what I want. Thanks in anticipation.

Thanks Bob, that looked good. Unfortunately I am getting the error #N/A , any
other ideas.


"Bob Phillips" wrote:

Assuming your table is in A1:E4, and the lookup length is in H1, and the
lookup width is in H2, try this

=INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0))

Do all of your lookup values exist in the row/column headings? If not, what
would you expect to get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LesLdh" wrote in message
...
Thanks Bob, that looked good. Unfortunately I am getting the error #N/A ,
any
other ideas.


"Bob Phillips" wrote:

Assuming your table is in A1:E4, and the lookup length is in H1, and the
lookup width is in H2, try this

=INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0))

Yes, the lookup values do exist in the row/column headings.

"Bob Phillips" wrote:

Do all of your lookup values exist in the row/column headings? If not, what
would you expect to get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LesLdh" wrote in message
...
Thanks Bob, that looked good. Unfortunately I am getting the error #N/A ,
any
other ideas.


"Bob Phillips" wrote:

Assuming your table is in A1:E4, and the lookup length is in H1, and the
lookup width is in H2, try this

=INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0))

I used your data in my test, and it worked fine. What values do you have in
H1 and H2?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LesLdh" wrote in message
...
Yes, the lookup values do exist in the row/column headings.

"Bob Phillips" wrote:

Do all of your lookup values exist in the row/column headings? If not,
what
would you expect to get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LesLdh" wrote in message
...
Thanks Bob, that looked good. Unfortunately I am getting the error
#N/A ,
any
other ideas.


"Bob Phillips" wrote:

Assuming your table is in A1:E4, and the lookup length is in H1, and
the
lookup width is in H2, try this

=INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0))

If you arrange your lengths and widths in descending order, you won't
need all the values. For example, with the table changed to:

40 35 30 25
35 9.2 8.3 7.5 6.15
30 8.3 7.85 6.2 5.15
25 7.1 6.35 5.2 4.6

Use the formula:
=INDEX(Matrix!B2:E4,MATCH(H1,Matrix!A2:A4,-1),MATCH(H2,Matrix!B1:E1,-1))

LesLdh wrote:
Yes, the lookup values do exist in the row/column headings.

"Bob Phillips" wrote:


Do all of your lookup values exist in the row/column headings? If not, what
would you expect to get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LesLdh" wrote in message
...

Thanks Bob, that looked good. Unfortunately I am getting the error #N/A ,

any

other ideas.


"Bob Phillips" wrote:


Assuming your table is in A1:E4, and the lookup length is in H1, and the
lookup width is in H2, try this

=INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1, 0))






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Some of your numbers may in fact be text.
--

Ron P

Sometimes you're the windshield:)
Sometimes you're the bug:(


"LesLdh" wrote in message
...
Thanks Bob, that looked good. Unfortunately I am getting the error #N/A ,
any
other ideas.


"Bob Phillips" wrote:

Assuming your table is in A1:E4, and the lookup length is in H1, and the
lookup width is in H2, try this

=INDEX(A1:E4,MATCH(H1,A1:A4,0),MATCH(H2,A1:E1,0))