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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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


OCONUS said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top