# 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
###### Who is Participating?

Commented:
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

Commented:
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

Commented:
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

Commented:
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

Author Commented:
I think it would need to be reversed or it would never = Federal CS??
0

Commented:
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

Author Commented:
yes so it could meat both criteria and I would never get Federal CS

It needs to check I3 first then B3
0

Commented:
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

Author 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

Author 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

Commented:
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

Commented:
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

Author 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

Commented:
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

Author 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

Commented:
Are you able to attach your file?  You can delete or change irrelevant columns if they are confidential.
0

Author Commented:
sample data
sampledata.xlsx
0

Author Commented:
in that example J3-5,7&8 should be Federal Consulting
J6,9&10 should be USPS Intel
0

Author Commented:
okay now it is working, what changed?
0

Commented:
Nothing at all.  I used the formula I recommended in post ID: 39651649 above.  And you repasted 6 posts up from here...
0

Commented:
...and wasn't the formula I posted the first working solution?

regards, barry
0

Commented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.