VLOOKUP #NoMatch has me completely stumped

Liz Castellaw

I have a vlookup formula in my sheet (Project Plan) that references another sheet (Phone List). Despite triple checking formulas, spelling, spaces, etc. I could not get the formula to return anything by #No Match. I copied and pasted the original sheet (Phone List) that's referenced into a new sheet (Copy of Phone List) and now the formula works. I really don't want to have two separate sheets with the exact same information. Please help me figure out why the formula in Project Plan doesn't work when I reference Phone List! Thank you! (The only thing I can think of is I only have View permission on the original phone list.)

coby

Have you tried setting the 'match_type' to FALSE? often when I'm getting issues like that, setting the match-type to false fixes the issue.

Liz Castellaw

Thank you for the suggestion! The match type is already set to false. 😕

Paul Newcome

Double check that your ranges are the same for both formulas.

Liz Castellaw

I double checked and they are the same width (include same number of columns) and reference the same column for the return value.

Paul Newcome

If everything is completely identical including ranges and whatnot and it is still not working properly, I would suggest reaching out to support.

Genevieve P.

Hi @Liz Castellaw

It looks like your formula is working when the content you're matching is in the Primary Column. Can you adjust your first sheet to have the Primary Column be the "Position & Division" column?

Liz Castellaw

Thanks, everyone, for your help! I just figured it out. The formula used to create the reference call in the master phone list joins a text column with a dropdown column whereas the copy of the phone list joins a text column with a text column (as does the sheet with the vlookup). Although the result of the formula is text, it still caused issues. So strange! I don't understand why that's the case. I did a copy/paste values from the Phone List and copy/paste values from the Copy Phone List and then did an if formula to see if the pasted values are the same and they are. Despite that, the problem occurred from the Phone List formula joining a dropdown field rather than text field. 🤨