Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
which formula? Match? IF?
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
which formula? Match? IF?
shay34 wrote:
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
which formula? Match? IF?
Try this:
=IF(OR(LEFT(A1,5)="12345",LEFT(A1,5)="23456"),"Blu e",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 On Nov 13, 4:40*pm, shay34 wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
which formula? Match? IF?
=IF(OR(LEFT(A1,5)={"12345","23456"}),"Blue",IF(OR( LEFT(A1,5)={"34567","45678"}),"Red","Neither
red nor blue")) -- David Biddulph "shay34" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
which formula? Match? IF?
There are 24 different group numbers in the Red plan and 15 different group
numbers in the Blue plan. "Glenn" wrote: shay34 wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Match+sum formula | Excel Discussion (Misc queries) | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
MATCH Formula | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |