Trying to match a text string to a data table, any suggestions?

[Guest]

Trying to match the first six characters of a text string to text characters
in a data table. I'm trying to use the VLOOKUP Function, but I keep getting
the contents of the second to last cell in my designated column. I'm having
a problem both with truncating the text string, and also using text to search
for things. Anyway to convert similar text characters to unique numbers?
Looking for any suggestions

 

[Peo Sjoblom]

Are you using FALSE or 0 as lookup type? If not, that can explain it,
otherwise post the formulas and a little bit more info on what you are
trying to do

 

[Guest]

I'm using TRUE because I don't have exact matches. I'm trying to figure out
how I could get exact matches but I would need to eliminate part of the
origional text string. For example, I'm trying to match zzz-abcdefg to the
data source which has only zzz-abcd. Here is an example of a formula I'm
trying:
=VLOOKUP(P5,[DATA.xls]Sheet1!$1:$65536,6,TRUE)

I'm also considering the OR(EXACT function as a possibility, but I'm not
sure if I can find the corresponding data for the cell in the array that
makes the function return TRUE.

thanks for your help

 

[Peo Sjoblom]

Is the value always the first 8 characters? If So

=VLOOKUP(LEFT(P5,8),[DATA.xls]Sheet1!$A$1:$F$1000,6,0)

if the zzz-abcdefg is in the table instead of P5 and the beginning is
always the same you can use a wildcard

=VLOOKUP(P5&"*",[DATA.xls]Sheet1!$A$1:$F$1000,6,0)

I noted that you use the whole workbook 1:65536, I strongly recommend using
something more precise if possible like in my example

I'm using TRUE because I don't have exact matches. I'm trying to figure out
how I could get exact matches but I would need to eliminate part of the
origional text string. For example, I'm trying to match zzz-abcdefg to the
data source which has only zzz-abcd. Here is an example of a formula I'm
trying:
=VLOOKUP(P5,[DATA.xls]Sheet1!$1:$65536,6,TRUE)

I'm also considering the OR(EXACT function as a possibility, but I'm not
sure if I can find the corresponding data for the cell in the array that
makes the function return TRUE.

thanks for your help

Are you using FALSE or 0 as lookup type? If not, that can explain it,
otherwise post the formulas and a little bit more info on what you are
trying to do


--

Regards,

Peo Sjoblom