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

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

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

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

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

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

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