IF & Vlookup

toni.forrest

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?

Brent Wilson

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 Wilson

@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 Wilson

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))

toni.forrest

You are a legend! Thank you, it works now:)

Brent Wilson

@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.

toni.forrest

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

1. Find if the product [Product Number & Name] exists in the {Item Master Range}
2. If found, AND [Ex Works] = Yes, to return a specific column value from the {Item Master Range}
3. If found, AND [Ex Works] = No, to return a different column value from the {Item Master Range}
4. If found, AND [Ex Works] = Blank, to state "Select Ex Works Option"
5. 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.

Brent Wilson

@toni.forrest

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)

1. The default match type in a VLOOKUP is true.. an Approximate Match..
   1. So if it cannot find Employ 12345 employee 12344 is "close enough" and will be returned
   2. With a False match, it should throw an error as 12345 is missing
   3. https://help.smartsheet.com/function/vlookup
2. 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?