Home

New Computer and copy of Excel, now VLOOKUP isn't working

%3CLINGO-SUB%20id%3D%22lingo-sub-666236%22%20slang%3D%22en-US%22%3ENew%20Computer%20and%20copy%20of%20Excel%2C%20now%20VLOOKUP%20isn't%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666236%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%26nbsp%3B%20I%20used%20Vlookup%20all%20the%20time.%26nbsp%3B%20Got%20a%20new%20computer%20and%20copy%20of%20Excel%20and%20now%20it%20doesn't%20work.%26nbsp%3B%20I've%20been%20testing%20for%20about%205%20hours%20and%20narrowed%20it%20down%20to%20a%20text%20issue.%26nbsp%3B%26nbsp%3B%20Tried%20the%20usual%20getting%20rid%20of%20trailing%20blank%20spaces%2C%20text%20format%2C%20LEN%20function%20to%20make%20sure%20the%20lenth%20was%20correct.%26nbsp%3B%20Built%20a%20simple%20spreadsheet%20to%20test.%26nbsp%3B%20Will%20work%20with%20numbers%2C%20but%20not%20Text.%26nbsp%3B%20Any%20ideas%20are%20appreciates.%26nbsp%3B%20This%20is%20what%20I%20get%20with%20my%20spreadsheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20C%3C%2FP%3E%3CP%3E1%26nbsp%3B%26nbsp%3B%20101%20Michael%26nbsp%3B%20Blue%3C%2FP%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%20102%20Cindy%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Green%3C%2FP%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%20103%20Sam%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Yellow%3C%2FP%3E%3CP%3E4%26nbsp%3B%26nbsp%3B%20104%20Ashley%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Red%3C%2FP%3E%3CP%3E5%26nbsp%3B%26nbsp%3B%20105%20Amanda%26nbsp%3B%20Orange%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(A3%2CA1%3AC5%2C3%2C0)%26nbsp%3B%20returns%20Yellow%3C%2FP%3E%3CP%3E%3DVLOOKUP(B3%2CA1%3AC5%2C3%2C0)%26nbsp%3B%20returns%20%23N%2FA%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan't%20understand%20why%20it%20won't%20match%20it's%20own%20text%20in%20the%20same%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-666236%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-666272%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Computer%20and%20copy%20of%20Excel%2C%20now%20VLOOKUP%20isn't%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354093%22%20target%3D%22_blank%22%3E%40Desertanalyst%3C%2FA%3E%26nbsp%3B%20Duh.%26nbsp%3B%26nbsp%3B%20Sometimes%20you%20just%20have%20to%20step%20away.%26nbsp%3B%26nbsp%3B%20The%20search%20string%20has%20to%20be%20in%20the%20first%20column.%26nbsp%3B%26nbsp%3B%20The%20new%20datasets%20I%20was%20sent%20have%20them%20in%20the%20second%20column.%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666441%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Computer%20and%20copy%20of%20Excel%2C%20now%20VLOOKUP%20isn't%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666441%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354093%22%20target%3D%22_blank%22%3E%40Desertanalyst%3C%2FA%3E%26nbsp%3BVLOOKUP%20has%20a%20limitation%2C%20it%20works%20from%20Left%20to%20Right%20direction%20only%2C%20and%20it%20must%20start%20with%201st%20column.%3CBR%20%2F%3EYou%20may%20use%20Match%20%2B%20Index%20functions%20for%20more%20flexibility.%3CBR%20%2F%3ETry%20this%3A%26nbsp%3B%3DINDEX(%24C%241%3A%24C%245%2CMATCH(B3%2C%24B%241%3A%24B%245))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Desertanalyst
New Contributor

Hi All,  I used Vlookup all the time.  Got a new computer and copy of Excel and now it doesn't work.  I've been testing for about 5 hours and narrowed it down to a text issue.   Tried the usual getting rid of trailing blank spaces, text format, LEN function to make sure the lenth was correct.  Built a simple spreadsheet to test.  Will work with numbers, but not Text.  Any ideas are appreciates.  This is what I get with my spreadsheet:

 

       A       B           C

1   101 Michael  Blue

2   102 Cindy      Green

3   103 Sam        Yellow

4   104 Ashley     Red

5   105 Amanda  Orange

 

=VLOOKUP(A3,A1:C5,3,0)  returns Yellow

=VLOOKUP(B3,A1:C5,3,0)  returns #N/A

 

Can't understand why it won't match it's own text in the same cell.

2 Replies

@Desertanalyst  Duh.   Sometimes you just have to step away.   The search string has to be in the first column.   The new datasets I was sent have them in the second column.  Thanks!

@Desertanalyst VLOOKUP has a limitation, it works from Left to Right direction only, and it must start with 1st column.
You may use Match + Index functions for more flexibility.
Try this: =INDEX($C$1:$C$5,MATCH(B3,$B$1:$B$5))

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 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
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies