vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-752497%22%20slang%3D%22en-US%22%3Evlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3ETrying%20to%20do%20a%20vlookup%20on%20a%20table%20that%20is%201000%20rows%20and%2026%20columns.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20I%20try%20to%20get%20the%20value%20in%20the%2013th%20column%2C%20it%20works%20fine.%20However%2C%20if%20I%20try%20for%20any%20column%20greater%20than%20that%20I%20get%20a%20%23REF!%20error.%20All%20columns%20have%20valid%20data.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFormula%20being%20used%20is%20%3DVLOOKUP(%24a1%2CTableSheet!%24A%3A%24Z%2C15%2CFALSE).%20Any%20ideas%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-752497%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-752510%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752510%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375485%22%20target%3D%22_blank%22%3E%40duglee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EWe%20get%20a%20%23Ref%20error%20if%20we%20refer%20to%20something%20that%20does%20not%20exist%20(e.g.)%20a%20named%20range%20that%20has%20been%20deleted)%3C%2FP%3E%3CP%3EAlthough%20your%20formula%20looks%20correct%20I%20need%20to%20look%20at%20a%20sample%20file%20to%20check%20the%20issue%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-752544%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20reply.%20Turns%20out%20there%20was%20a%20duplicate%20line%20higher%20up%20in%20the%20table%20that%20was%20causing%20the%20error.%20Sorry%20to%20trouble%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-752550%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375485%22%20target%3D%22_blank%22%3E%40duglee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20problem%20my%20friend%3C%2FP%3E%3CP%3EGlad%20you%20resolved%20it%3C%2FP%3E%3CP%3ELike%20my%20reply%20if%20you%20want%20%3A)%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
duglee
New Contributor

Trying to do a vlookup on a table that is 1000 rows and 26 columns.

If I try to get the value in the 13th column, it works fine. However, if I try for any column greater than that I get a #REF! error. All columns have valid data.

Formula being used is =VLOOKUP($a1,TableSheet!$A:$Z,15,FALSE). Any ideas?

3 Replies

@duglee 

Hi

We get a #Ref error if we refer to something that does not exist (e.g.) a named range that has been deleted)

Although your formula looks correct I need to look at a sample file to check the issue

Thanks

Nabil Mourad

@nabilmourad Thanks for the reply. Turns out there was a duplicate line higher up in the table that was causing the error. Sorry to trouble you.

@duglee 

No problem my friend

Glad you resolved it

Like my reply if you want :)

Thanks

Nabil Mourad

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies