why won't VLOOKUP search entire word?

[petro]

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)

 

[Corticus]

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)

 

[petro]

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.

 

[Corticus]

Oops,

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

Hope thats it!

HTH

 

[petro]

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

 

[maxflia10]

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

 

[Mark W.]

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

 

[Aladin Akyurek]

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

forgot to add,
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.

Aladin

 

[petro]

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

 

[EdE]

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!