Two way Lookup

[Guest]

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?

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

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

 

[Guest]

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

 

[Guest]

I did of course mean

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



Mike

 

[Guest]

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.

 

[T. Valko]

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.

 

[Ragdyer]

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

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

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(columnlookupvalue,ColumnRange,1))

Will post back the results of your suggestions later today

Thanks once again!