Thread
:
Two way Lookup
View Single Post
#
2
Posted to microsoft.public.excel.misc
Sandy Mann
external usenet poster
Posts: 2,345
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?
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann