which formula? Match? IF?

S

shay34

I am trying to figure which formula to use for the following type of
scenario. I've simplified it to make it easier to understand the concept.

Column A has a five digit "group" number followed by dash and another five
"individual" number (ex: 12345-67890). However, the first five digits are not
all the same because there are multiple "group" numbers.

I want to be able to create a formula in Column B that analyzes the first
five numbers in Column A, and yield "Blue" or "Red".

If the first five numbers are either "12345" or "23456", I want the formula
to yield "Blue" in Column B.

If the first five numbers are "34567" or "45678", I want the formula to
yield "Red" in Column B.

Which formula should I use and how would I write it? THANKS!
 
G

Glenn

shay34 said:
I am trying to figure which formula to use for the following type of
scenario. I've simplified it to make it easier to understand the concept.

Column A has a five digit "group" number followed by dash and another five
"individual" number (ex: 12345-67890). However, the first five digits are not
all the same because there are multiple "group" numbers.

I want to be able to create a formula in Column B that analyzes the first
five numbers in Column A, and yield "Blue" or "Red".

If the first five numbers are either "12345" or "23456", I want the formula
to yield "Blue" in Column B.

If the first five numbers are "34567" or "45678", I want the formula to
yield "Red" in Column B.

Which formula should I use and how would I write it? THANKS!

How many options are there? You could set up a lookup table on another sheet
with Group numbers in column A and "colors" in column B, then:

=VLOOKUP(LEFT(A2,5),GroupSheet!$A$1:$B$13,2,FALSE)
 
P

Pete_UK

Try this:

=IF(OR(LEFT(A1,5)="12345",LEFT(A1,5)="23456"),"Blue",IF(OR(LEFT(A1,5)
="34567",LEFT(A1,5)="45678"),"Red","colourless"))

If you have a lot more categories than this it would be better to use
VLOOKUP and a table somewhere that listed them all.

Hope this helps.

Pete
 
D

David Biddulph

=IF(OR(LEFT(A1,5)={"12345","23456"}),"Blue",IF(OR(LEFT(A1,5)={"34567","45678"}),"Red","Neither
red nor blue"))
 
S

shay34

There are 24 different group numbers in the Red plan and 15 different group
numbers in the Blue plan.
 

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