combining IF statement with Vlookup

mikes6058
mikes6058 used Ask the Experts™
on
I would like a formula to do the following;

Formula to feature in cell G2

IF cell E2 contains either of these three values, Pending, Awaiting supplier confirmation, SBA sent | Awaiting return, then return an email address found in the range K2:L35.

The email address must correspond to the name found in cell A2. If no value is present in A2 then return a blank.

If cell E2 contains any value other than the three stated above then also return a blank

This formula will be copied down for each row

Thanks Mike
combining-IF-Statements-with-vlookup.ods
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Rgonzo1971
Top Expert 2016

Commented:
Hi,

pls try

=IF(OR(E2="Pending",E2="Awaiting supplier confirmation",E2="SBA sent | Awaiting return"),VLOOKUP(A2,$K$2:$L$35,2),"")

Open in new window

Regards
Ryan ChongSoftware Team Lead
Commented:
in cell G2, try formula:

=IF( OR(E2="Pending",E2="Awaiting supplier confirmation",E2="SBA sent | Awaiting return"), VLOOKUP(A2,K:L,2,FALSE), "")

Open in new window


and drag this formula down accordingly.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Try this.....

In G2
=IFERROR(IF(OR(ISNUMBER(SEARCH({"Pending","Awaiting supplier confirmation","SBA sent | Awaiting return"},E2))),VLOOKUP(A2,$K$2:$L$35,2,0),""),"")

Open in new window

and copy down.
mikes6058

Author

Commented:
Both do the job perfectly

Thanks
Mike
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Mike! Glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial