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

**Kim75** · 05-26-2016, 05:28 PM

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.

**Pete_UK** · 05-26-2016, 05:42 PM

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

**mikeTRON** · 05-26-2016, 05:46 PM

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