IF & Vlookup
Hi there, I am trying to complete a VLOOKUP with an IF Statement but its returning a blank despite data being available in the cross-reference sheet
=IF([Ex Works]@row = "Yes"; VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 3; VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 2)))
What am I doing wrong?
Best Answers
-
I think you are missing your closing bracket on the first Vlookup
You have
VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 3; VLOOKUP....
It should be ([Product Number & Name]@row; {Item Master Range 1}; 3); VLOOKUP...
Try this one
=IF([Ex Works]@row = "Yes"; VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 3); VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 2))
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
@toni.forrest If you write a lot of IF statements you might want to look at a notepad tool.
Something like Notepad++ is free and it highlights the matching brackets in a statement.
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Answers
-
I think you are missing your closing bracket on the first Vlookup
You have
VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 3; VLOOKUP....
It should be ([Product Number & Name]@row; {Item Master Range 1}; 3); VLOOKUP...
Try this one
=IF([Ex Works]@row = "Yes"; VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 3); VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 2))
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
You are a legend! Thank you, it works now:)
-
@toni.forrest If you write a lot of IF statements you might want to look at a notepad tool.
Something like Notepad++ is free and it highlights the matching brackets in a statement.
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Thank you, I downloaded Notepad ++
I think I have omitted a critical element in my formula, as its returning values even if the [Product Number & Name] is NOT FOUND, in the Item Master Range.
I've tried inserting an IFERROR, return "0" but thats not working.
Basically, I need to
- Find if the product [Product Number & Name] exists in the {Item Master Range}
- If found, AND [Ex Works] = Yes, to return a specific column value from the {Item Master Range}
- If found, AND [Ex Works] = No, to return a different column value from the {Item Master Range}
- If found, AND [Ex Works] = Blank, to state "Select Ex Works Option"
- if the product [Product Number & Name] does not exist in the {Item Master Range} to return a 0
I have confused myself to no end on this simple formula.
-
Try this
=IFERROR(IF(Ex Works]@row = "Yes"; VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 3;False), VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 2;False));0)
- The default match type in a VLOOKUP is true.. an Approximate Match..
- So if it cannot find Employ 12345 employee 12344 is "close enough" and will be returned
- With a False match, it should throw an error as 12345 is missing
- https://help.smartsheet.com/function/vlookup
- Then the IFERROR should catch the ERROR and put a Zero.
Sorry this is untested so let me know how it goes
Might be a good check to see is it really returning the correct data or is it Employee 12344 as I said above?
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
- The default match type in a VLOOKUP is true.. an Approximate Match..
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!