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.
