VLOOKUP formula pulling data from another sheet

jwilson

I'm using the VLOOKUP Formula to match the "Application" in table 1 to "Application Name" in table 2, then return the "Primary Product Family" from Table 2, Column 2 to "Program" in Table 1 where the formula is.

=VLOOKUP(Application@row, {Applications}, 2, false)

What I'm I doing wrong?

Jgorsich

Vlookup sometimes has very odd problems - here is another example.

https://community.smartsheet.com/discussion/77503/vlookup-nomatch-has-me-completely-stumped

Looking at what you've got, it seems like the problems only happen when your application name doesn't start with a 0. Given that Smartsheet tries to automatically differentiate between a numeric value and a text value, and given that a leading 0 makes it 100% certain it is treating it as text, I'm betting the problem lies it it considering one of the two application numbers to be a numeric value and the other to be text.

If that is the case, making a helper column in each sheet that was "="app" + application@row" or "="app" + [application name]@row" and then using your formula on those helper columns instead would force it to treat everything as text and might solve the issue.

Also - just a suggestion - index(match()) tends to have fewer issues than Vlookup.

bisaacs

Hey @jwilson,

Have you tried using INDEX/COLLECT in the Program colum? That seems to work for me:

=INDEX(COLLECT({Product Family Column}, {Application Name Column}, Application@row), 1)

Hope this helps!

jwilson

Thank you for your answer. So if I was going to use INDEX(MATCH()), how would I write the formula?

jwilson

@Jgorsich I created a helper column in both sheets and it worked. Thank you for your help