Home

VLOOKUP not working - plese help.

%3CLINGO-SUB%20id%3D%22lingo-sub-810000%22%20slang%3D%22en-US%22%3EVLOOKUP%20not%20working%20-%20plese%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810000%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20running%20a%20VLOOKUP%20formula%20over%20multiple%20rows%20and%20it%20is%20all%20fine%20until%20it%20gets%20to%20the%2035th%20row%20and%20after%20that%20is%20produces%26nbsp%3B%20%23N%2FA%20%26nbsp%3B%26nbsp%3B%20.%20%26nbsp%3B%20is%2034%20the%20limit%20of%20how%20many%20times%20you%20can%20copy%20and%20paste%3F%26nbsp%3B%20I%20have%20tried%20everything%20and%20it%20all%20keeps%20ending%20after%2034.%26nbsp%3B%20please%20help.%26nbsp%3B%20I%20have%20attached%20the%20example%20file%20here..%20%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrustrated%20in%20Seattle.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-810000%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-810069%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20not%20working%20-%20plese%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810069%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394103%22%20target%3D%22_blank%22%3E%40hedleyinwa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20is%20stopping%20because%20it%20has%20reached%20the%20end%20of%20the%20table%20array%20in%20the%20VLOOKUP%20formula%2C%20%24A%245%3A%24P%2438.%20You%20must%20change%20the%20table%20array%20to%20include%20the%20entire%20lookup%20table.%20In%20your%20specific%20case%20this%20would%20be%20%24A%245%3A%24P%2455.%20The%20VLOOKUP%20should%20be%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(%24A5%2CFOOD_DIRECT!%24A%245%3A%24P%2455%2C2%2CFALSE)%3C%2FP%3E%3C%2FLINGO-BODY%3E
hedleyinwa
Occasional Visitor

I am running a VLOOKUP formula over multiple rows and it is all fine until it gets to the 35th row and after that is produces  #N/A    .   is 34 the limit of how many times you can copy and paste?  I have tried everything and it all keeps ending after 34.  please help.  I have attached the example file here..  

thank you

 

Frustrated in Seattle.

 

 

1 Reply

 @hedleyinwa 

Your formula is stopping because it has reached the end of the table array in the VLOOKUP formula, $A$5:$P$38. You must change the table array to include the entire lookup table. In your specific case this would be $A$5:$P$55. The VLOOKUP should be this:

 

=VLOOKUP($A5,FOOD_DIRECT!$A$5:$P$55,2,FALSE)

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies