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?