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

Copper 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))