# How to combine multiple functions possibly an if/or OR if/and statment

1. ## How to combine multiple functions possibly an if/or OR if/and statment

I am certain that there is an easy way to do this, in my real task I have approx 40 items in column A, so trying to find an efficient formula to return a value in column C

Here is sample data
A B C
apples 100
orange 200
pear 300
grape 400
banana 500
kiwi 600

for column C value
apples, orange, grape are all to divide column B by 10
pear, to divide column B by 5
banana and kiwi, to divide column b 15

I know I can write this
=IF(A2="apples",B2/10,IF(A2="orange",B2/10,""))

But because in my real work I have multiple that have the same dividing factor I am looking for a way to shorten the formula.

2. ## Re: How to combine multiple functions possibly an if/or OR if/and statment

You can set up a small table somewhere which lists your 40 unique items in one column and next to each you can list the divisor, like this:

apples ... 10
orange ...10
grape .....10
pear .......5
banana ..15
kiwi .......15
and so on.

Suppose you put this in columns M and N. Then you can use this formula in C2:

=IFERROR(B2/VLOOKUP(A2,\$M:\$N,2,0),"")

then copy down as required.

Hope this helps.

Pete

3. ## Re: How to combine multiple functions possibly an if/or OR if/and statment

I would use a vlookup and a reference table.

Each fruit has it's own value to be divided by, so you can just look that up. This is essentially how I handle most IF statements if I can, because it is SO simple to manage and understand.
See the attached for a simple example.

EDIT: See, Pete thinks the same above

4. ## Re: How to combine multiple functions possibly an if/or OR if/and statment

Originally Posted by mikeTRON
... EDIT: See, Pete thinks the same above ...
I think that's 3 times in the last 24 hours, Mike - people will start talking !! <bg>

Pete

5. ## Re: How to combine multiple functions possibly an if/or OR if/and statment

Originally Posted by Pete_UK
I think that's 3 times in the last 24 hours, Mike - people will start talking !! <bg>

Pete
I'm just riding your coattail... and/or I need to respond to these simple requests faster haha.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1