|
IF or VLOOKUP or whatever
I have a spreadsheet using IF statement but now find I am limited to 7
statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
On the RATES sheet...
A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 .... .... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
Something is still not working. Here is what I have - Sheet 1
A B C Los Angeles CA $1,735,000 Santa Ana CA $400,000 Denver CO $350,000 Sheet 2 is RATES A C San Francisco 0.0329 Washington 0.0329 Boston 0.0329 Austin 0.0293 San Antonio 0.0293 Seattle 0.0293 Phoenix 0.0293 Los Angeles 0.0316 Denver 0.0293 Portland 0.0293 Cincinnati 0.0293 Sacramento 0.0293 All Others 0.0262 Here is what I tried: =VLOOKUP(A1,'RATES'!A2:A14,3,'RATES'!c14) So If the City on Sheet 1 is not listed between C2:C14 then I need to use the factor in RATES C14. Also, what if my Cities are not listed in A1 but in Column E as an example. Is Column A my only option? -- TL Fitz "JLatham" wrote: On the RATES sheet... A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 ... ... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
Try this instead, say in D1
=IF(ISNA(VLOOKUP(A1,'RATES'!A$1:C$14,3,FALSE)),VLO OKUP("All Others",'RATES'!A$1:A$14,3,FALSE),VLOOKUP(A1,'RATE S'!A$1:C$14,3,FALSE)) I'm assuming on RATES sheet there is really a column B between A and C, so we want to get the value from column c, 3rd column in group A, B, C. The IF( statement lets us make a test and then make a decision based on the results (True or False) of that test. the ISNA(VLOOKUP(A1,'RATES'!A$1:C$14,3,FALSE)), is the test which says if the VLOOKUP can't find a matching city, then (true - can't find it), do this part: VLOOKUP("All Others",'RATES'!A$1:A$14,3,FALSE), which says look in that table for the "All Others" entry and return the value in the same row from the 3rd column of the table, which turns out to be column C. Remember that it is just a coincidence that C is also column #3 as you count across, the value is the number of the column in the table. If our table went from B to D instead of A to C, then we would return the value in column D. But if the NA() test failed (meaning we DID match the city), then do this: VLOOKUP(A1,'RATES'!A$1:C$14,3,FALSE) which returns our value for the city. The ",FALSE)" portion of the VLOOKUP statement just says that the list of cities in the table does not have to be in alphabetical order, so it will work with your list. "TL" wrote: Something is still not working. Here is what I have - Sheet 1 A B C Los Angeles CA $1,735,000 Santa Ana CA $400,000 Denver CO $350,000 Sheet 2 is RATES A C San Francisco 0.0329 Washington 0.0329 Boston 0.0329 Austin 0.0293 San Antonio 0.0293 Seattle 0.0293 Phoenix 0.0293 Los Angeles 0.0316 Denver 0.0293 Portland 0.0293 Cincinnati 0.0293 Sacramento 0.0293 All Others 0.0262 Here is what I tried: =VLOOKUP(A1,'RATES'!A2:A14,3,'RATES'!c14) So If the City on Sheet 1 is not listed between C2:C14 then I need to use the factor in RATES C14. Also, what if my Cities are not listed in A1 but in Column E as an example. Is Column A my only option? -- TL Fitz "JLatham" wrote: On the RATES sheet... A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 ... ... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
Added note: when you enter the formula, make sure you use (and dont use) the
$ symbol where I have. The dollar sign keeps the row numbers in the range/table referenced on the RATES sheet from changing as you fill the formula down the other sheet. Leaving it out of the (A1, part of the formulas, lets that row number modify itself as you fill it down the page. "TL" wrote: Something is still not working. Here is what I have - Sheet 1 A B C Los Angeles CA $1,735,000 Santa Ana CA $400,000 Denver CO $350,000 Sheet 2 is RATES A C San Francisco 0.0329 Washington 0.0329 Boston 0.0329 Austin 0.0293 San Antonio 0.0293 Seattle 0.0293 Phoenix 0.0293 Los Angeles 0.0316 Denver 0.0293 Portland 0.0293 Cincinnati 0.0293 Sacramento 0.0293 All Others 0.0262 Here is what I tried: =VLOOKUP(A1,'RATES'!A2:A14,3,'RATES'!c14) So If the City on Sheet 1 is not listed between C2:C14 then I need to use the factor in RATES C14. Also, what if my Cities are not listed in A1 but in Column E as an example. Is Column A my only option? -- TL Fitz "JLatham" wrote: On the RATES sheet... A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 ... ... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
It is still not working for me so I did a simple sheet with 5 cities in A
then on sheet2 same 5 cities in A and rates in C. The program does want to add a * on the last VLOOKUP. =IF(ISNA(VLOOKUP(A1,RATES!A$1:C$5,3,FALSE)),VLOOKU P("All Others",RATES!A$1:C$5,3,FALSE)*VLOOKUP(A1,RATES!A$ 1:C$5,3,FALSE)) The answer is coming out FALSE. It is probably the operator at this point but I just can't see it. -- TL Fitz "JLatham" wrote: Added note: when you enter the formula, make sure you use (and dont use) the $ symbol where I have. The dollar sign keeps the row numbers in the range/table referenced on the RATES sheet from changing as you fill the formula down the other sheet. Leaving it out of the (A1, part of the formulas, lets that row number modify itself as you fill it down the page. "TL" wrote: Something is still not working. Here is what I have - Sheet 1 A B C Los Angeles CA $1,735,000 Santa Ana CA $400,000 Denver CO $350,000 Sheet 2 is RATES A C San Francisco 0.0329 Washington 0.0329 Boston 0.0329 Austin 0.0293 San Antonio 0.0293 Seattle 0.0293 Phoenix 0.0293 Los Angeles 0.0316 Denver 0.0293 Portland 0.0293 Cincinnati 0.0293 Sacramento 0.0293 All Others 0.0262 Here is what I tried: =VLOOKUP(A1,'RATES'!A2:A14,3,'RATES'!c14) So If the City on Sheet 1 is not listed between C2:C14 then I need to use the factor in RATES C14. Also, what if my Cities are not listed in A1 but in Column E as an example. Is Column A my only option? -- TL Fitz "JLatham" wrote: On the RATES sheet... A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 ... ... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
Primary reason you're getting FALSE is that you've not included what to do if
the ISNA() test turns out to be false. As I understand the problem, you want to lookup the rate from column C on the Rates sheet based on the City and multiply that rate by the value in column C on Sheet 1 (the presumed property value? or whatever). Taking the data you've shown here in your last posting, I duplicated the workbook here and have tested this formula which you should put into D1 on Sheet1 (not on the Rates sheet) =IF(ISNA(VLOOKUP(A1,Rates!A$1:C$14,3,FALSE)),VLOOK UP("All Others",Rates!A$1:C$14,3,FALSE)*C1,VLOOKUP(A1,Rate s!A$1:C$14,3,FALSE)*C1) Just fill it down the sheet, the numbers I come up with in column D are $54,826 $10,480 $10,255 I hope this helps. I realize that VLOOKUP and HLOOKUP can be confusing and it looks like it's managed to confuse you. This should straighten things out, I hope. "TL" wrote: It is still not working for me so I did a simple sheet with 5 cities in A then on sheet2 same 5 cities in A and rates in C. The program does want to add a * on the last VLOOKUP. =IF(ISNA(VLOOKUP(A1,RATES!A$1:C$5,3,FALSE)),VLOOKU P("All Others",RATES!A$1:C$5,3,FALSE)*VLOOKUP(A1,RATES!A$ 1:C$5,3,FALSE)) The answer is coming out FALSE. It is probably the operator at this point but I just can't see it. -- TL Fitz "JLatham" wrote: Added note: when you enter the formula, make sure you use (and dont use) the $ symbol where I have. The dollar sign keeps the row numbers in the range/table referenced on the RATES sheet from changing as you fill the formula down the other sheet. Leaving it out of the (A1, part of the formulas, lets that row number modify itself as you fill it down the page. "TL" wrote: Something is still not working. Here is what I have - Sheet 1 A B C Los Angeles CA $1,735,000 Santa Ana CA $400,000 Denver CO $350,000 Sheet 2 is RATES A C San Francisco 0.0329 Washington 0.0329 Boston 0.0329 Austin 0.0293 San Antonio 0.0293 Seattle 0.0293 Phoenix 0.0293 Los Angeles 0.0316 Denver 0.0293 Portland 0.0293 Cincinnati 0.0293 Sacramento 0.0293 All Others 0.0262 Here is what I tried: =VLOOKUP(A1,'RATES'!A2:A14,3,'RATES'!c14) So If the City on Sheet 1 is not listed between C2:C14 then I need to use the factor in RATES C14. Also, what if my Cities are not listed in A1 but in Column E as an example. Is Column A my only option? -- TL Fitz "JLatham" wrote: On the RATES sheet... A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 ... ... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
OK, it multiplies out the rates from the RATES sheet with the value on sheet
1 for the cities listed on both sheets as long as they are in the appropriate order. On the city that is not listed on the RATES page, it will not pick up the all other rate and multiply it out. What I have is probably about 18 cities with specific rates and over 300 locations. Sheet 1 will list all the locations. In addition, can I restate the actual rate in another column on Sheet 1 that it pulled from the RATES sheet? Another question, will this only work if the Cities are listed in A1? My spreadsheet has numerous columns of information and the Cities do not actually reside in A1 yet for the purpose of testing, I have made everything start in A1. I really appreciate your help on this. I'm using a spreadsheet that has been existing for about 5 years, however the IF statements can no longer be used as I have to nest to many to work. -- TL Fitz "JLatham" wrote: Primary reason you're getting FALSE is that you've not included what to do if the ISNA() test turns out to be false. As I understand the problem, you want to lookup the rate from column C on the Rates sheet based on the City and multiply that rate by the value in column C on Sheet 1 (the presumed property value? or whatever). Taking the data you've shown here in your last posting, I duplicated the workbook here and have tested this formula which you should put into D1 on Sheet1 (not on the Rates sheet) =IF(ISNA(VLOOKUP(A1,Rates!A$1:C$14,3,FALSE)),VLOOK UP("All Others",Rates!A$1:C$14,3,FALSE)*C1,VLOOKUP(A1,Rate s!A$1:C$14,3,FALSE)*C1) Just fill it down the sheet, the numbers I come up with in column D are $54,826 $10,480 $10,255 I hope this helps. I realize that VLOOKUP and HLOOKUP can be confusing and it looks like it's managed to confuse you. This should straighten things out, I hope. "TL" wrote: It is still not working for me so I did a simple sheet with 5 cities in A then on sheet2 same 5 cities in A and rates in C. The program does want to add a * on the last VLOOKUP. =IF(ISNA(VLOOKUP(A1,RATES!A$1:C$5,3,FALSE)),VLOOKU P("All Others",RATES!A$1:C$5,3,FALSE)*VLOOKUP(A1,RATES!A$ 1:C$5,3,FALSE)) The answer is coming out FALSE. It is probably the operator at this point but I just can't see it. -- TL Fitz "JLatham" wrote: Added note: when you enter the formula, make sure you use (and dont use) the $ symbol where I have. The dollar sign keeps the row numbers in the range/table referenced on the RATES sheet from changing as you fill the formula down the other sheet. Leaving it out of the (A1, part of the formulas, lets that row number modify itself as you fill it down the page. "TL" wrote: Something is still not working. Here is what I have - Sheet 1 A B C Los Angeles CA $1,735,000 Santa Ana CA $400,000 Denver CO $350,000 Sheet 2 is RATES A C San Francisco 0.0329 Washington 0.0329 Boston 0.0329 Austin 0.0293 San Antonio 0.0293 Seattle 0.0293 Phoenix 0.0293 Los Angeles 0.0316 Denver 0.0293 Portland 0.0293 Cincinnati 0.0293 Sacramento 0.0293 All Others 0.0262 Here is what I tried: =VLOOKUP(A1,'RATES'!A2:A14,3,'RATES'!c14) So If the City on Sheet 1 is not listed between C2:C14 then I need to use the factor in RATES C14. Also, what if my Cities are not listed in A1 but in Column E as an example. Is Column A my only option? -- TL Fitz "JLatham" wrote: On the RATES sheet... A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 ... ... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
I'm kind of at a loss as to why it's not working properly - but the fact that
we're dealing with a 5 yr old file probably plays into it somehow. An example file with it working as I understand it has been uploaded to he http://www.jlathamsite.com/uploads/for_tl.xls This should work for as many entries on sheet 1 as you care to put - you just fill the formula in D1 down the sheet. Search Excel Help for fill data in worksheet cells a topic with that same title should come up, may not be the first in the list. The cities don't have to be listed in column A on sheet1, they could be in any column, just change the references to A1 in that first formula to the proper cell, same for the value in C1, if it's in a different cell on the real deal, just change the reference to where it really is. The file also has examples with things located in different places on Sheet1 "TL" wrote: OK, it multiplies out the rates from the RATES sheet with the value on sheet 1 for the cities listed on both sheets as long as they are in the appropriate order. On the city that is not listed on the RATES page, it will not pick up the all other rate and multiply it out. What I have is probably about 18 cities with specific rates and over 300 locations. Sheet 1 will list all the locations. In addition, can I restate the actual rate in another column on Sheet 1 that it pulled from the RATES sheet? Another question, will this only work if the Cities are listed in A1? My spreadsheet has numerous columns of information and the Cities do not actually reside in A1 yet for the purpose of testing, I have made everything start in A1. I really appreciate your help on this. I'm using a spreadsheet that has been existing for about 5 years, however the IF statements can no longer be used as I have to nest to many to work. -- TL Fitz "JLatham" wrote: Primary reason you're getting FALSE is that you've not included what to do if the ISNA() test turns out to be false. As I understand the problem, you want to lookup the rate from column C on the Rates sheet based on the City and multiply that rate by the value in column C on Sheet 1 (the presumed property value? or whatever). Taking the data you've shown here in your last posting, I duplicated the workbook here and have tested this formula which you should put into D1 on Sheet1 (not on the Rates sheet) =IF(ISNA(VLOOKUP(A1,Rates!A$1:C$14,3,FALSE)),VLOOK UP("All Others",Rates!A$1:C$14,3,FALSE)*C1,VLOOKUP(A1,Rate s!A$1:C$14,3,FALSE)*C1) Just fill it down the sheet, the numbers I come up with in column D are $54,826 $10,480 $10,255 I hope this helps. I realize that VLOOKUP and HLOOKUP can be confusing and it looks like it's managed to confuse you. This should straighten things out, I hope. "TL" wrote: It is still not working for me so I did a simple sheet with 5 cities in A then on sheet2 same 5 cities in A and rates in C. The program does want to add a * on the last VLOOKUP. =IF(ISNA(VLOOKUP(A1,RATES!A$1:C$5,3,FALSE)),VLOOKU P("All Others",RATES!A$1:C$5,3,FALSE)*VLOOKUP(A1,RATES!A$ 1:C$5,3,FALSE)) The answer is coming out FALSE. It is probably the operator at this point but I just can't see it. -- TL Fitz "JLatham" wrote: Added note: when you enter the formula, make sure you use (and dont use) the $ symbol where I have. The dollar sign keeps the row numbers in the range/table referenced on the RATES sheet from changing as you fill the formula down the other sheet. Leaving it out of the (A1, part of the formulas, lets that row number modify itself as you fill it down the page. "TL" wrote: Something is still not working. Here is what I have - Sheet 1 A B C Los Angeles CA $1,735,000 Santa Ana CA $400,000 Denver CO $350,000 Sheet 2 is RATES A C San Francisco 0.0329 Washington 0.0329 Boston 0.0329 Austin 0.0293 San Antonio 0.0293 Seattle 0.0293 Phoenix 0.0293 Los Angeles 0.0316 Denver 0.0293 Portland 0.0293 Cincinnati 0.0293 Sacramento 0.0293 All Others 0.0262 Here is what I tried: =VLOOKUP(A1,'RATES'!A2:A14,3,'RATES'!c14) So If the City on Sheet 1 is not listed between C2:C14 then I need to use the factor in RATES C14. Also, what if my Cities are not listed in A1 but in Column E as an example. Is Column A my only option? -- TL Fitz "JLatham" wrote: On the RATES sheet... A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 ... ... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
IF or VLOOKUP or whatever
Most likely it is not working because I'm blonde, and that's just a truth.
Clearly it is the operator. Thank you for the link. This is perfect for me as I am a visual. Thank you for your time and patience. TL -- TL Fitz "JLatham" wrote: I'm kind of at a loss as to why it's not working properly - but the fact that we're dealing with a 5 yr old file probably plays into it somehow. An example file with it working as I understand it has been uploaded to he http://www.jlathamsite.com/uploads/for_tl.xls This should work for as many entries on sheet 1 as you care to put - you just fill the formula in D1 down the sheet. Search Excel Help for fill data in worksheet cells a topic with that same title should come up, may not be the first in the list. The cities don't have to be listed in column A on sheet1, they could be in any column, just change the references to A1 in that first formula to the proper cell, same for the value in C1, if it's in a different cell on the real deal, just change the reference to where it really is. The file also has examples with things located in different places on Sheet1 "TL" wrote: OK, it multiplies out the rates from the RATES sheet with the value on sheet 1 for the cities listed on both sheets as long as they are in the appropriate order. On the city that is not listed on the RATES page, it will not pick up the all other rate and multiply it out. What I have is probably about 18 cities with specific rates and over 300 locations. Sheet 1 will list all the locations. In addition, can I restate the actual rate in another column on Sheet 1 that it pulled from the RATES sheet? Another question, will this only work if the Cities are listed in A1? My spreadsheet has numerous columns of information and the Cities do not actually reside in A1 yet for the purpose of testing, I have made everything start in A1. I really appreciate your help on this. I'm using a spreadsheet that has been existing for about 5 years, however the IF statements can no longer be used as I have to nest to many to work. -- TL Fitz "JLatham" wrote: Primary reason you're getting FALSE is that you've not included what to do if the ISNA() test turns out to be false. As I understand the problem, you want to lookup the rate from column C on the Rates sheet based on the City and multiply that rate by the value in column C on Sheet 1 (the presumed property value? or whatever). Taking the data you've shown here in your last posting, I duplicated the workbook here and have tested this formula which you should put into D1 on Sheet1 (not on the Rates sheet) =IF(ISNA(VLOOKUP(A1,Rates!A$1:C$14,3,FALSE)),VLOOK UP("All Others",Rates!A$1:C$14,3,FALSE)*C1,VLOOKUP(A1,Rate s!A$1:C$14,3,FALSE)*C1) Just fill it down the sheet, the numbers I come up with in column D are $54,826 $10,480 $10,255 I hope this helps. I realize that VLOOKUP and HLOOKUP can be confusing and it looks like it's managed to confuse you. This should straighten things out, I hope. "TL" wrote: It is still not working for me so I did a simple sheet with 5 cities in A then on sheet2 same 5 cities in A and rates in C. The program does want to add a * on the last VLOOKUP. =IF(ISNA(VLOOKUP(A1,RATES!A$1:C$5,3,FALSE)),VLOOKU P("All Others",RATES!A$1:C$5,3,FALSE)*VLOOKUP(A1,RATES!A$ 1:C$5,3,FALSE)) The answer is coming out FALSE. It is probably the operator at this point but I just can't see it. -- TL Fitz "JLatham" wrote: Added note: when you enter the formula, make sure you use (and dont use) the $ symbol where I have. The dollar sign keeps the row numbers in the range/table referenced on the RATES sheet from changing as you fill the formula down the other sheet. Leaving it out of the (A1, part of the formulas, lets that row number modify itself as you fill it down the page. "TL" wrote: Something is still not working. Here is what I have - Sheet 1 A B C Los Angeles CA $1,735,000 Santa Ana CA $400,000 Denver CO $350,000 Sheet 2 is RATES A C San Francisco 0.0329 Washington 0.0329 Boston 0.0329 Austin 0.0293 San Antonio 0.0293 Seattle 0.0293 Phoenix 0.0293 Los Angeles 0.0316 Denver 0.0293 Portland 0.0293 Cincinnati 0.0293 Sacramento 0.0293 All Others 0.0262 Here is what I tried: =VLOOKUP(A1,'RATES'!A2:A14,3,'RATES'!c14) So If the City on Sheet 1 is not listed between C2:C14 then I need to use the factor in RATES C14. Also, what if my Cities are not listed in A1 but in Column E as an example. Is Column A my only option? -- TL Fitz "JLatham" wrote: On the RATES sheet... A B C 1 Alburquerque 1.95 2.98 2 Boston 2.10 1.75 3 Chicago 2.08 1.81 4 Denver 1.96 1.58 ... ... 100 Yonkers 3.15 2.01 on the other sheet A 1 Denver and Denver is in the list over on RATES sheet Any cell on not-Rates sheet =VLOOKUP(A1,'RATES'!A1:C100,2,FALSE) A1 is what you want to find on the RATES sheet (Denver), 'RATES'!A1:C100 is the whole range that is going to be examined/used, but the city name must be in the first column of this area (in column A) 2, is the column number to get information back from the 'table' on the Rates sheet, since column B is the 2nd column in the group A:C, you'll get 1.96 returned. If the city in A1 on this sheet doesn't match anything in column A on the RATES sheet you will get #NA. "TL" wrote: I have a spreadsheet using IF statement but now find I am limited to 7 statements max. Here is what I have. A=City another tab (RATES) on spreadsheet breaksdown rates based on cities. I need : Find the rate that applies to the city listed. There are 12 Cities then an all other rate for cities not listed. I know this can be done, but I just can't figure it out. Thanks -- TL Fitz |
| All times are GMT +1. The time now is 11:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com