Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia (row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

With the table in A1:F4, 10 in H1, 220 in H2 try:

=VLOOKUP(H1,A2:F4,(CEILING(H2,50))/50)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"neil" wrote in message
...
Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia
(row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to
be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

Since you seem to want the next highest depth when an exact match is not
found you need to reverse the order of your table.

See this screencap:

http://img409.imageshack.us/img409/6089/lookuptp4.jpg

--
Biff
Microsoft Excel MVP


"neil" wrote in message
...
Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia
(row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to
be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

Hi,

Try this:-
=SUMPRODUCT((A2:A4=30)*(B1:F1=400)*(B2:F4))

This would find the intersect of 30 * 300 = 15

It would be better to use cell references for the 30 and 400 instead of
changing the formula.

Mike


"neil" wrote:

Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia (row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

I did of course mean

=SUMPRODUCT((A2:A4=30)*(B1:F1=300)*(B2:F4))

:)

Mike

"neil" wrote:

Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia (row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

Given that your table:

Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

occupies cells A1:F5, have the user enter a diameter value in say cell A10
and depth in B10, then you could have the following formula in cell A11 to
return your price:

=VLOOKUP(A10,A2:F5,ROUNDUP(B10/50,50)+1,FALSE)

Hope this helps.

"neil" wrote:

Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia (row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

You need to adjust for the column offset but this could lead to incorrect
results.

(CEILING(H2,50))/50)

If the depth was 20 the above evaluates to 1. Column 1 of the table is
A2:A4. If you adjust for the offset by adding 1:

1+(CEILING(H2,50))/50)

Then this will return the incorrect result when an exact match is found.

--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
With the table in A1:F4, 10 in H1, 220 in H2 try:

=VLOOKUP(H1,A2:F4,(CEILING(H2,50))/50)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"neil" wrote in message
...
Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia
(row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to
be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

This should work even if your depths are *not* equally spaced:

=INDEX(A1:F4,MATCH(H1,A1:A4,0),MATCH(SMALL(A1:F1,C OUNTIF(A1:F1,"<"&H2)+1),A1
:F1,0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"neil" wrote in message
...
Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia
(row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to
be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

Good catch Biff - I never thought about the depth being less than the
minimum.

To correct this and to save the OP reversing the table as in your suggestion
I would madify it to:

=VLOOKUP(H1,A2:F4,(MAX(2,CEILING(H2,50)/50)))

--
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
You need to adjust for the column offset but this could lead to incorrect
results.

(CEILING(H2,50))/50)

If the depth was 20 the above evaluates to 1. Column 1 of the table is
A2:A4. If you adjust for the offset by adding 1:

1+(CEILING(H2,50))/50)

Then this will return the incorrect result when an exact match is found.

--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
With the table in A1:F4, 10 in H1, 220 in H2 try:

=VLOOKUP(H1,A2:F4,(CEILING(H2,50))/50)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"neil" wrote in message
...
Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia
(row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to
be
displayed to them. While the diameter data is usually a set value that
is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?

Thank you all for the suggestions! Im trying out each one to see if they
solve this problem.

In the meanwhile, I chanced upon the offset Match formula by Chip on his
website..
I get the result I desire by using Offset(FirstCellinTable,
match(rowlookupvalue,RowsRange,0),match(columnlook upvalue,ColumnRange,1))

Will post back the results of your suggestions later today

Thanks once again!

"Ragdyer" wrote:

This should work even if your depths are *not* equally spaced:

=INDEX(A1:F4,MATCH(H1,A1:A4,0),MATCH(SMALL(A1:F1,C OUNTIF(A1:F1,"<"&H2)+1),A1
:F1,0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"neil" wrote in message
...
Hi,

I have created a matrix of rates for a combination of diameters (rows) &
depth (columns). Increments of the column data is in 50mm, while the dia
(row
data) is fixed values. The matrix is hidden to users.

Users provide a combination of diameter & depth, and require the rate to
be
displayed to them. While the diameter data is usually a set value that is
already defined in the price list, the depth can be any value.

The spreadsheet looks somewhat like this
Depth upto
Dia 100 150 200 250 300
10 1 2 3 4 5
20 2 4 6 8 10
30 3 6 9 12 15

Suppose a user wants to know the price for 10dia by 220 depth, I need to
provide the intersect of row 1 & column 4 (result 4).

Can you please help me do this two-way lookup?