Jun 03 2019 10:19 PM
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.
Jun 03 2019 10:42 PM
@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!
Jun 04 2019 12:28 AM
@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))