# why won't VLOOKUP search entire word?

#### petro

##### New Member
Hi guys,

I'm using a Vlookup formula to access a table and return my value. However when it's searching the table it seems to only look for the first 2 letters in the word, because of this when it searches for the word Toronto, it returns the value from Tokyo, since it appears first on the list. Here is my code, what did I miss to make it search for the whole word? Thanks.

VLOOKUP(A1,[EquityDataID.xls]Sheet1!\$A\$2:\$B\$36,2)

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Without the last True/False argument of the Vookup function, Excel will find the closest match.

Try:
VLOOKUP(A1,[EquityDataID.xls]Sheet1!\$A\$2:\$B\$36,2,False)

Thanks, put that in, it no longer gives me the value for Tokyo, but now for Toronto instead of returning the proper value, or even the Tokyo value it returns #N/A. Any thoughts? I checked to make sure that Toronto was in the range of the lookup, as shown in my formula above it's A2:B36 and the Toronto match is in row 35.

Oops,

your list needs to be sorted for Vlookup to find an exact match.

Hope thats it!

HTH

nope, just sorted in ascending order and I still get #N/A for Toronto

From the Help Files

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

You probably have either or both leading or trailing spaces

No. The table_array does not have to be sorted while performing an exact match. In addition to maxflia10's comments make sure that the data type of the values in the leftmost column of the table_array is the same as the data type of the lookup_value.
This message was edited by Mark W. on 2002-10-16 16:57

On 2002-10-16 16:49, Corticus wrote:
Oops,

your list needs to be sorted for Vlookup to find an exact match.

Hope thats it!

HTH

Corticus,

=VLOOKUP(lookup-value,lookup-table,column-index,0)

doesn't require the first column of the lookup-table to be sorted in order to do an exact match.

There are maybe extraneous spaces or other non-visible chars around the values in the first column of the lookup table that thwart the matching process.

that did it, thanks a lot!!, when Toronto got pulled in from another spreadsheet it had a trailing space. **** that's exact. Heh, you guys saved my life (and hair) again

You could also use Index and Match.
=index([DataId.xls]Sheet1!a2:b36,match(a1,[DataId.xls]sheet1!a2:a36),2)

I think that is the correct syntax. I use something similar to this all the time with part numbers.

HTH!

Replies
18
Views
1K
Replies
16
Views
711
Replies
8
Views
340
Replies
1
Views
163
Replies
20
Views
584

1,203,460
Messages
6,055,557
Members
444,797
Latest member
18ecooley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back