lookup functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KD - 12 May 2008 21:05 GMT

I need to return a value at the intersection of a row and column when the
value in a ro and a seperate value in a column is met. I can't find a way to
do this when the values are based on a variable in a certain cell. For
example:

If the dwelling value is 30,999 and is in protection class 5 then I need to
return the value at the intersection of these two variables (the answer is
461) but how do I write that formula. The dwelling value and protection class
would come from the same cell's each time but the values in those cells would
be different.

    Protection Class
DWELLING VALUES    1    2    3    4    5
25,000-25,999    405    405    405    405    405
26000-26999    417    417    417    417    417
27000-27999    430    430    430    430    430
28000-28999    443    443    443    443    443
29000-29999    452    452    452    452    452
30000-30999    461    461    461    461    461
31000-31999    470    470    470    470    470
32000-32999    479    479    479    479    479
33000-33999    486    486    486    486    486
34000-34999    493    493    493    493    493
35000-35999    500    500    500    500    500
36000-36999    508    508    508    508    508
37000-37999    514    514    514    514    514
38000-38999    520    520    520    520    520
39000-39999    526    526    526    526    526
40000-40999    532    532    532    532    532
41000-41999    538    538    538    538    538
42000-42999    544    544    544    544    544
43000-43999    551    551    551    551    551
44000-44999    558    558    558    558    558
45000-45999    565    565    565    565    565
46000-46999    572    572    572    572    572
47000-47999    579    579    579    579    579
48000-48999    586    586    586    586    586

Reply to this Message

Sandy Mann - 12 May 2008 21:25 GMT

You will need to use the full Dwelling Value of 30000-30999 in J1 and 5 in
K1 in the VLOOKUP()
formula:

=VLOOKUP(J1,A3:F26,MATCH(K1,B2:F2)+1,FALSE)

Signature

HTH

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

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

>I need to return a value at the intersection of a row and column when the
> value in a ro and a seperate value in a column is met. I can't find a way
[quoted text clipped - 37 lines]
> 47000-47999 579 579 579 579 579
> 48000-48999 586 586 586 586 586

Reply to this Message

T. Valko - 12 May 2008 21:44 GMT

For the dwelling values use the *lower boundary* of each interval:

25000
26000
27000
28000

Also, your protection class values are the same for each level no matter
what class they're in. So, create a 2 column table like this:

25000...405
26000...417
27000...430
28000...443

Assume this table is in the range A1:B24

H1 = 30999

=VLOOKUP(H1,A1:B24,2)

Signature

Biff
Microsoft Excel MVP

Reply to this Message

Ron Rosenfeld - 12 May 2008 21:47 GMT

>I need to return a value at the intersection of a row and column when the
>value in a ro and a seperate value in a column is met. I can't find a way to
[quoted text clipped - 33 lines]
>47000-47999 579 579 579 579 579
>48000-48999 586 586 586 586 586

If your Table is NAME's tbl, and set up similar to above, except with only the
lower part of the Dwelling Value range in column 1, then:

=VLOOKUP(DV,Tbl,PC+1)

A problem with this formula is that if the DV is >48000, the values from the
last row will be returned. One way out would be to add another row to the
table:

48000 586 586 586 586 586
49000 #N/A #N/A #N/A #N/A #N/A

(and be sure to reNAME Tbl to include that last row.

Of course, given your data, the Protection Class is irrelevant as they are all
identical for a given Dwelling Value. So for this particular case, the formula
could be simplified: =VLOOKUP(DV,Tbl,2)
I suspect your real data does not have this characteristic, however :-)
--ron

Reply to this Message

KD - 12 May 2008 22:38 GMT

Actually only the values in the example are the same. My protection classes
go to 10 and because it started word wrapping into the other part of my table
in my example I only took the table over to 5. In the actual table there are
differences as you go across the columns. I also failed to add that I have
multiple tables. I have made sure that all the data ranges for each table are
in the same cells. I've created the worksheet/table name with a concatenate
formula and put that in a cell. So I should just be able to reference that
cell for the worksheet/table.

> >I need to return a value at the intersection of a row and column when the
> >value in a ro and a seperate value in a column is met. I can't find a way to
[quoted text clipped - 53 lines]
> I suspect your real data does not have this characteristic, however :-)
> --ron

Reply to this Message

Ron Rosenfeld - 13 May 2008 01:57 GMT

>Actually only the values in the example are the same. My protection classes
>go to 10 and because it started word wrapping into the other part of my table
[quoted text clipped - 4 lines]
>formula and put that in a cell. So I should just be able to reference that
>cell for the worksheet/table.

Then my first formula should work
--ron

Reply to this Message

KD - 12 May 2008 23:38 GMT

I tried the formula =VLOOKUP(B25,'c8'!A5:K181,MATCH(B7,'c8'!B5:K5)+1,FALSE)
because B25 is the dwelling value and B7 is the protection class. I get a
#N/A error. I don't know if this helps. The formula and criteria are in one
worksheet. The data I'm looking up is in other worksheets depending on a cell
naming the worksheet to look at. But they are all in the same workbook.

> I need to return a value at the intersection of a row and column when the
> value in a ro and a seperate value in a column is met. I can't find a way to
[quoted text clipped - 33 lines]
> 47000-47999 579 579 579 579 579
> 48000-48999 586 586 586 586 586

Reply to this Message

Sandy Mann - 12 May 2008 23:56 GMT

It looks like it is the MATCH() function that is returning the error:

=VLOOKUP(J1,'C8'!A5:K181,MATCH(K1,'C8'!B4:K4,FALSE)+1,FALSE)

Works for me.

Possibly a better formula would be:

=VLOOKUP(J1,'C8'!A4:K181,MATCH(K1,'C8'!B4:K4,FALSE)+1,FALSE)

With the "DWELLING VALUES" from your sample in Cell A4

Signature

HTH

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

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

>I tried the formula =VLOOKUP(B25,'c8'!A5:K181,MATCH(B7,'c8'!B5:K5)+1,FALSE)
> because B25 is the dwelling value and B7 is the protection class. I get a
[quoted text clipped - 46 lines]
>> 47000-47999 579 579 579 579 579
>> 48000-48999 586 586 586 586 586

Reply to this Message