asked on

Help with Excel Formula

I have the following excel formula

=IF(OR(LEFT(C2,3)="CS-",LEFT(C2,4)="CS -"),"Consulting & Intelligence",LOOKUP(9.9999E+307,SEARCH({"Civilian","ES USPS CONSULTING SERVICES AND INTEL","Defense","Navy","Healthcare","Sled"},M2),{"Civilian","USPS Intel","Defense/Fedsec","Navy/USMC","Healthcare","S&L"}))

Couple questions...

1. what is the purpose of LOOKUP(9.9999E+307
2. also I need to add a check that would also look in column Q and any time the value USPSF016741 is found the the return should be Intel

SOLUTION

Karthik Ramachandran

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rgonzo1971

HI,

pls try

=IF(Q2="USPSF016741";"Intel";IF(OR(LEFT(C2,3)="CS-",LEFT(C2,4)="CS -"),"Consulting & Intelligence",LOOKUP(9.9999E+307,SEARCH({"Civilian","ES USPS CONSULTING SERVICES AND INTEL","Defense","Navy","Healthcare","Sled"},M2),{"Civilian","USPS Intel","Defense/Fedsec","Navy/USMC","Healthcare","S&L"})))

Open in new window

Regards

Matt Pinkston

ASKER

This formula

=IF(Q2="USPSF016741";"Intel";IF(OR(LEFT(C2,3)="CS-",LEFT(C2,4)="CS -"),"Consulting & Intelligence",LOOKUP(9.9999E+307,SEARCH({"Civilian","ES USPS CONSULTING SERVICES AND INTEL","Defense","Navy","Healthcare","Sled"},M2),{"Civilian","USPS Intel","Defense/Fedsec","Navy/USMC","Healthcare","S&L"})))

gets an error

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.