what can I do if I need to nest more than 7 formulas?

M

Maggie

I have a spreadsheet to keep track of sales tax information. I have a drop
down list in the first column to choose the customer, and I want the next
several columns to fill in automatically with the customer's address and some
other information. I did fine with a nested IF formula until I reached the
limit of 7. We have more than 7 customers who we charge sales tax, and I need
to hit them all somehow
 
D

Duke Carey

You need to use the VLOOKUP() function, or perhaps the HLOOKUP() function.

You're not specific about how you are using the IF()s, but one of the lookup
variants should give you what you want.

You might also explore MATCH() and INDEX()
 
R

Ronald Dodge

Rather than useing IF, THEN, ELSE type function for this sort of thing, you
would actually be better served using one of the lookup functions such as
MATCH or VLOOKUP, or in more complex situations, even using the
DB<Functions> to get what you are looking for.

One such case would be the following:

Table contained is customer name in column "A", and their sales tax rate in
column "B"

You give column A the range name of "DBCustomerName"
You give column B the range name of "DBCustomerSalesTax"

This is assuming there is nothing else in those 2 columns of that particular
worksheet.


Now you are having G6 with the customer name and you want H6 to fill in
automatically with the sales tax percentage based on the customer name in
G6. Here's the formula to be in H6:

=IF(ISERROR(MATCH(G6,DBCustomerName,0)),"",INDIRECT(ADDRESS(MATCH(G6,DBCustomerName,0),COLUMN(DBCustomerSalesTax))))

Ronald R. Dodge, Jr.
Master MOUS 2000
 
N

Niek Otten

Hi Maggie,

Look here:

http://www.cpearson.com/excel/nested.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a spreadsheet to keep track of sales tax information. I have a drop
| down list in the first column to choose the customer, and I want the next
| several columns to fill in automatically with the customer's address and some
| other information. I did fine with a nested IF formula until I reached the
| limit of 7. We have more than 7 customers who we charge sales tax, and I need
| to hit them all somehow
 
M

Maggie

Basically using it to fill in info from another sheet. Ex:
IF(b9=customer1,customer1 address,IF(b9=customer2,customer2 address.....etc.

I don't really get how VLOOKUP and HLOOKUP work.
 
B

Bernard Liengme

Just the thing for VLOOKUP.
Read Help; buy a book; experiment; come back when you have tried
best wishes
 
K

krcowen

Maggie
Anybody who can handle 6 nested If statements can learn any of the
methods noted above.
Ken
 
M

Maggie

I figured vlookup out. Thank you!!!

Duke Carey said:
You need to use the VLOOKUP() function, or perhaps the HLOOKUP() function.

You're not specific about how you are using the IF()s, but one of the lookup
variants should give you what you want.

You might also explore MATCH() and INDEX()
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top