Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup functions
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup functions
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) -- Biff Microsoft Excel MVP "KD" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup functions
On Mon, 12 May 2008 13:05:01 -0700, KD wrote:
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 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup functions
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. "Ron Rosenfeld" wrote: On Mon, 12 May 2008 13:05:01 -0700, KD wrote: 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 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup functions
I tried the formula =VLOOKUP(B25,'c8'!A5:K181,MATCH(B7,'c8'!B5:K5)+1,F ALSE)
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. "KD" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup functions
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 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "KD" wrote in message ... I tried the formula =VLOOKUP(B25,'c8'!A5:K181,MATCH(B7,'c8'!B5:K5)+1,F ALSE) 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. "KD" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup functions
On Mon, 12 May 2008 14:38:00 -0700, KD wrote:
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. Then my first formula should work --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup functions | Excel Discussion (Misc queries) | |||
help on lookup functions | Excel Worksheet Functions | |||
Lookup Functions | Excel Worksheet Functions | |||
LOOKUP functions? | Excel Worksheet Functions | |||
Lookup Functions | Excel Worksheet Functions |