SOLVED

Why isn't my vlookup returning most of the values?

%3CLINGO-SUB%20id%3D%22lingo-sub-2852174%22%20slang%3D%22en-US%22%3EWhy%20isn't%20my%20vlookup%20returning%20most%20of%20the%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2852174%22%20slang%3D%22en-US%22%3E%3CP%3EAfter%20I%20refresh%20my%20database%20Query%201%20table%2C%20I%20try%20to%20lookup%20the%20slic%20column%20in%20Table2%20to%20get%20the%20description%20in%20Table3.%20However%2C%20Only%20one%20of%20the%20descriptions%20is%20coming%20back.%20Any%20ideas%3F%20To%20be%20more%20exact%2C%20why%20am%20I%20getting%20%23N%2FA%20in%20column%20F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rodsan724_0-1634365784844.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317844i039168530212EAA1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rodsan724_0-1634365784844.png%22%20alt%3D%22rodsan724_0-1634365784844.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2852174%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2852247%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20isn't%20my%20vlookup%20returning%20most%20of%20the%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2852247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E%26nbsp%3BMost%20likely%20the%2010%2C%2020%20and%2030%20are%20not%20the%20same%20type%20in%20both%20tables.%20One%20could%20be%20text%20and%20the%20other%20a%20number.%20Or%20if%20they%20are%20text%20in%20both%20tables%2C%20one%20could%20have%20one%20or%20more%20trailing%20spaces.%20Hence%2C%20%23N%2FA%20as%20VLOOKUP%20doesn't%20find%20an%20exact%20match.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2852755%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20isn't%20my%20vlookup%20returning%20most%20of%20the%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2852755%22%20slang%3D%22en-US%22%3E%3CP%3EI%20attached%20the%20file%20to%20OP.%20I%20wasn't%20sure%20if%20I%20should%20l%20leave%20the%20data%20source%20so%20I%20deleted%20it.%20I%20can%20put%20back%20in%20if%20you%20like%2C%20I%20wasn't%20sure%20if%20the%20values%20would%20come%20in%20without%20the%20data%20source.%20I%20have%20control%20over%20Table3%20but%20not%20the%20data%20source.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2852778%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20isn't%20my%20vlookup%20returning%20most%20of%20the%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2852778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E%26nbsp%3BCan't%20really%20tell%20where%20it%20went%20wrong%2C%20but%20some%20the%20data%20types%20were%20not%20in%20sync.%20I%20made%20it%20all%20Text%20and%20redid%20the%20VLOOKUP.%20Now%20it%20seems%20to%20work%20as%20expected.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2852786%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20isn't%20my%20vlookup%20returning%20most%20of%20the%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2852786%22%20slang%3D%22en-US%22%3EThe%20only%20control%20I%20have%20is%20over%20Table%203.%20What%20do%20I%20have%20to%20do%20to%20fix%20it%3F%20If%20I%20double%20click%20the%20Id%20cell%20and%20press%20enter%20it%20seems%20to%20fix%20itself.%20However%2C%20in%20my%20production%20case%2C%20I%20have%20thousands%20of%20records%20like%20this%20and%20can't%20manually%20do%20it%20for%20all%20those%20rows.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2852822%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20isn't%20my%20vlookup%20returning%20most%20of%20the%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2852822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E%26nbsp%3BDon't%20know!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

After I refresh my database Query 1 table, I try to lookup the slic column in Table2 to get the description in Table3. However, Only one of the descriptions is coming back. Any ideas? To be more exact, why am I getting #N/A in column F?

 

rodsan724_0-1634365784844.png

I thought I could use ISNUMBER and NUMBERVALUE but it works in one case but not the other???

rodsan724_0-1634417650767.png

 

rodsan724_0-1634417947132.png

 

 

8 Replies

@rodsan724 Most likely the 10, 20 and 30 are not the same type in both tables. One could be text and the other a number. Or if they are text in both tables, one could have one or more trailing spaces. Hence, #N/A as VLOOKUP doesn't find an exact match.

I attached the file to OP. I wasn't sure if I should l leave the data source so I deleted it. I can put back in if you like, I wasn't sure if the values would come in without the data source. I have control over Table3 but not the data source.

@rodsan724 Can't really tell where it went wrong, but some the data types were not in sync. I made it all Text and redid the VLOOKUP. Now it seems to work as expected. See attached.

 

 

 

The only control I have is over Table 3. What do I have to do to fix it? If I double click the Id cell and press enter it seems to fix itself. However, in my production case, I have thousands of records like this and can't manually do it for all those rows.
I think I figured it out by changing the VLOOKUP using ISNUMBER and NUMBERVALUE
Nevermind, it half-worked updated OP
best response confirmed by rodsan724 (Contributor)
Solution
Figured it out!

=VLOOKUP(IF(ISNUMBER(VALUE(D2)),NUMBERVALUE(D2),D2),Table3,2,FALSE)

VLOOKUP
ISNUMBER
VALUE
NUMBERVALUE