Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 13th 08, 04:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3
Default 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   Report Post  
Old November 13th 08, 05:00 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1,240
Default 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   Report Post  
Old November 13th 08, 05:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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   Report Post  
Old November 13th 08, 05:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default 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   Report Post  
Old November 13th 08, 06:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw DS Excel Worksheet Functions 4 October 7th 06 12:25 AM
Match+sum formula Todd Nelson Excel Discussion (Misc queries) 1 September 27th 05 08:59 PM
How do I display more than one match in a Index/Match formula? Trish Excel Worksheet Functions 0 September 26th 05 10:21 PM
MATCH Formula Sandy Excel Worksheet Functions 3 August 12th 05 11:42 PM
Lookup Formula - but have a formula if it can't find/match a value Stephen Excel Worksheet Functions 11 June 14th 05 05:32 AM


All times are GMT +1. The time now is 01:47 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017