Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Excel Formula Extended Help

I need to add additional logic to an existing formula and need help...

My current formula is:
=LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"})

Which checks in the current column I and if certain values exist then the formula column is set to a specific value.

I also need to add logic that looks if column B begins with "CS-" or "CS -" and then sets the formula column to Federal CS
0
Matt Pinkston
Asked:
Matt Pinkston
  • 10
  • 9
  • 4
1 Solution
 
NBVCCommented:
Do you mean?

=IF(OR(B3="CS-",B3="CS -"),"Federal CS",LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"}))
0
 
barry houdiniCommented:
You could add an IF function to do that, i.e.

=IF(SUM(COUNTIF(B3,{"CS-*","CS -*"})),"Federal CS",LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"}))

regards, barry
0
 
NBVCCommented:
Shoot... I forgot that you were looking for "begins with".

Here is my udated formula....

=IF(OR(LEFT(B3,3)="CS-",LEFT(B3,4)="CS -"),"Federal CS",LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"}))
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Matt PinkstonAuthor Commented:
I think it would need to be reversed or it would never = Federal CS??
0
 
NBVCCommented:
The formula as we wrote them, checks if B3 begins with your states strings.... If it does then you will get "Federal CS", otherwise, it will continue on with the lookup in I3...

Is that not what you wanted?
0
 
Matt PinkstonAuthor Commented:
yes so it could meat both criteria and I would never get Federal CS

It needs to check I3 first then B3
0
 
NBVCCommented:
so, I you want it to check B3 if the I3 check returns an error?

Like:
=IFERROR(LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"}),IF(OR(LEFT(B3,3)="CS-",LEFT(B3,4)="CS -"),"Federal CS",""))
0
 
Matt PinkstonAuthor Commented:
No

From my first post

=LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"})

This gives me what I am looking for EXCEPT

After this, when B begins with "CS-" or "CS -" I need to set the column to Federal CS

With the solutions provided it is ALWAYS USPS Intel because when B begins with CS- or CS - it will also always have CONSULT in I3
0
 
Matt PinkstonAuthor Commented:
was thinking of something like this but does not work

=IF(LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"}),(LEFT(B3,3)="CS-",LEFT(B3,4)="CS -"),"Federal CS")
0
 
NBVCCommented:
I am getting lost here.

Can you give examples of possible combinations of what's in B3 and I3 and what you expect as results?
0
 
barry houdiniCommented:
Can you give an example where the the formula I suggested doesn't do what you want? From your description I don't see why you wouldn't check B3 first - in what circumstances would B3 start with CS- or CS - and the result needs to be something other than Federal CS?
0
 
Matt PinkstonAuthor Commented:
Okay in this example

Column B = CS - NSS - Option Yr 3
Column I = ES USPS CONSULTING SERVICES AND INTEL MANAGER

Result is = USPS Intel

Result should be Federal CS
0
 
NBVCCommented:
But that is what my original formula should do:

=IF(OR(LEFT(B3,3)="CS-",LEFT(B3,4)="CS -"),"Federal CS",LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"}))

In that sample, you would get "Federal CS"

If B3 was say just: NSS - Option Yr 3, then the formula result would be USPS Intel...
0
 
Matt PinkstonAuthor Commented:
if I use this formula

=IF(OR(LEFT(B3,3)="CS-",LEFT(B3,4)="CS -"),"Federal CS",LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I3),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"}))

all fields now = #N/A
0
 
NBVCCommented:
Are you able to attach your file?  You can delete or change irrelevant columns if they are confidential.
0
 
Matt PinkstonAuthor Commented:
sample data
sampledata.xlsx
0
 
Matt PinkstonAuthor Commented:
in that example J3-5,7&8 should be Federal Consulting
J6,9&10 should be USPS Intel
0
 
NBVCCommented:
I just plugged my formula into J3 and copied down... I got expected results.  Ie the first 3, the 5th and 6th result in "Federal CS", the others result in "USPS Intel"

Exactly as you just stated
Copy-of-sampledata.xlsx
0
 
Matt PinkstonAuthor Commented:
okay now it is working, what changed?
0
 
NBVCCommented:
Nothing at all.  I used the formula I recommended in post ID: 39651649 above.  And you repasted 6 posts up from here...
0
 
barry houdiniCommented:
...and wasn't the formula I posted the first working solution?

regards, barry
0
 
NBVCCommented:
Technically it was... but I did acknowledge that I neglected the "begins with" part and corrected my formula to suit.  I did follow through and stick with the OP so that he/she can finally understand that the solution(s) were correct to start.... but if you want the points, by all means, OP please transfer the points as you see fit...
0
 
barry houdiniCommented:
Hey, NB_VC!

My comment was more directed at pinkstonmp.......but it's over now, C'est la vie, as they say in Canada.

Points, you say, do you get points here, I never knew that?......:)

regards, barry
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now