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

Question

Hi All,&nbsp; I used Vlookup all the time.&nbsp; Got a new computer and copy of Excel and now it doesn't work.&nbsp; I've been testing for about 5 hours and narrowed it down to a text issue.&nbsp;&nbsp; Tried the usual getting rid of trailing blank spaces, text format, LEN function to make sure the lenth was correct.&nbsp; Built a simple spreadsheet to test.&nbsp; Will work with numbers, but not Text.&nbsp; Any ideas are appreciates.&nbsp; This is what I get with my spreadsheet:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C1&nbsp;&nbsp; 101 Michael&nbsp; Blue2&nbsp;&nbsp; 102 Cindy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Green3&nbsp;&nbsp; 103 Sam&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Yellow4&nbsp;&nbsp; 104 Ashley&nbsp;&nbsp;&nbsp;&nbsp; Red5&nbsp;&nbsp; 105 Amanda&nbsp; Orange&nbsp;=VLOOKUP(A3,A1:C5,3,0)&nbsp; returns Yellow=VLOOKUP(B3,A1:C5,3,0)&nbsp; returns #N/A&nbsp;Can't understand why it won't match it's own text in the same cell.

desertanalyst · Answer

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

bhushanz · Answer

Desertanalyst&nbsp;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:&nbsp;=INDEX($C$1:$C$5,MATCH(B3,$B$1:$B$5))

Forum Discussion

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

2 Replies

Resources