Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

ifs combined vlookup

Forum home » Delegate support and help forum » Microsoft Excel Training and help » IFs combined with Vlookup

IFs combined with Vlookup

ResolvedVersion 365

Douglas has attended:
Excel Intermediate course
Excel Power Query course
Excel Advanced course

IFs combined with Vlookup

How do I combined a IFs with a vlookup to return results.

I want the formula to perform the following:

> =IFS(B:B,"S3GB,S3NG,S3MZ",VLOOKUP(LEFT(B2,4),Mapping!A:C,3,0),
> =IFs if does not contain "S3GB,S3NG,S3MZ" it need to use this VLOOKUP(LEFT(B2,2),Mapping!A:C,3,0.

how do I write this command all in one formula?

RE: IFs combined with Vlookup

Hello Douglas,

Thank you for your question.

You can certainly combine the IF function with VLOOKUP in Excel to achieve this. Here's the formula that performs the check and returns the desired result:

=IF(OR(B2="S3GB", B2="S3NG", B2="S3MZ"), "", VLOOKUP(LEFT(B2,2), Mapping!A:C, 3, FALSE))

This formula works as follows:

OR(B2="S3GB", B2="S3NG", B2="S3MZ"): Checks if B2 contains any of the specified values.
IF(..., "", VLOOKUP(...)): If B2 contains one of the specified values, it returns an empty string (""). Otherwise, it performs the VLOOKUP function.
Feel free to test it out and let me know if you need any further adjustments!

I hope this helps.

Kind regards
Marius Barnard
STL

RE: IFs combined with Vlookup

Hi Marius

thanks for the quick reply.

however, that's half of the command. I need it to carryout to the following.

1st:

IF B2 contain any of these values (B2="S3GB", B2="S3NG", B2="S3MZ"), it should use this "VLOOKUP(LEFT(B2,4),Mapping!A:C,3,0"

2nd

If it doesn't contain (B2="S3GB", B2="S3NG", B2="S3MZ") then it should use this formula VLOOKUP(LEFT(B2,2), Mapping!A:C, 3, FALSE)

how do I combine this formula in one string?

thanks!
Douglas M

RE: IFs combined with Vlookup

Hi Douglas,

Please try the following formula to see if it works:

=IF(OR(B2="S3GB", B2="S3NG", B2="S3MZ"), VLOOKUP(LEFT(B2,4), Mapping!A:C, 3, 0), VLOOKUP(LEFT(B2,2), Mapping!A:C, 3, 0))

This IF formula now also contains a VLOOKUP in the TRUE part.

Kind regards
Marius

RE: IFs combined with Vlookup

not to worry I changed the formula to xlookup instead.

RE: IFs combined with Vlookup

Thank you Douglas, I'm glad that you got it to work.

Tue 1 Apr 2025: Automatically marked as resolved.

Excel tip:

Some examples of CTRL key shortcuts in Excel 2010

Did you know that the old CTRL key shortcuts haven't changed from previous versions of Excel to the 2010 version?

They remain exactly the same:

Ctrl+B for bold
Ctrl+I for italics
Ctrl+P to Print
Ctrl+S to Save

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.