software

I have an issue where I am trying to lookup certain text strings and return
values corresponding to the strings from a separate worksheet. However, the
strings do not match exactly on the worksheets. For example on the sheet
with the values to lookup it will list

quot;AAAA-BBBBquot;

but on the data worksheet it will have something like

quot;ZZAAAA-BBBB-YY

The text string is there, but using vlookup will not work as they do not
match exactly. In addition the lookup function doesn't seem to work as there
may be other strings in the data sheet such as

quot;AA-BBquot;

which is returned from the lookup function. Is there a way to find the
exact string within other strings as part of the array on the data sheet, or
is this not possible?

Please help.

Thank you,

Chaim

try using match with wildcard to find the row and then use in an index
function.

=INDEX(B15:C22,MATCH(quot;*c*quot;,B15:B22,0),2)

--
Don Guillett
SalesAid Software

quot;clubinquot; gt; wrote in message
...
gt;I have an issue where I am trying to lookup certain text strings and return
gt; values corresponding to the strings from a separate worksheet. However,
gt; the
gt; strings do not match exactly on the worksheets. For example on the sheet
gt; with the values to lookup it will list
gt;
gt; quot;AAAA-BBBBquot;
gt;
gt; but on the data worksheet it will have something like
gt;
gt; quot;ZZAAAA-BBBB-YY
gt;
gt; The text string is there, but using vlookup will not work as they do not
gt; match exactly. In addition the lookup function doesn't seem to work as
gt; there
gt; may be other strings in the data sheet such as
gt;
gt; quot;AA-BBquot;
gt;
gt; which is returned from the lookup function. Is there a way to find the
gt; exact string within other strings as part of the array on the data sheet,
gt; or
gt; is this not possible?
gt;
gt; Please help.
gt;
gt; Thank you,
gt;
gt; Chaim
you can't do it using the lookup functions, but you might be able to do it
with the index function.

I experiment with the following list starting in A1.
123a-b45
123a-g45
a-v123456

using the following formula you can find the row number of the item
containing quot;a-gquot;.
=SUMPRODUCT(--ISNUMBER(FIND(quot;a-gquot;,A1:A3)),ROW(A1:A3))
and if you wanted to match this number with an item in column B you could
use this formula...
=INDEX(B:B,SUMPRODUCT(--ISNUMBER(FIND(quot;a-gquot;,A1:A3)),ROW(A1:A3)))

This only works if the string is unique in the array. Other wise it will
return a funny number. You could use an if function using
=IF(SUMPRODUCT(--ISNUMBER(FIND(quot;a-gquot;,A1:A3)),ROW(A1:A3)/ROW(A1:3))=1,INDEX(B:B,SUMPRODUCT(--ISNUMBER(FIND(quot;a-gquot;,A1:A3)),ROW(A1:A3))),quot;error messagequot;)

For instance, if you had this list
123a-b45
123a-g45
a-v123456
123va-basd54

searching for quot;a-bquot; would give 5 (1 0 0 4) with the first formula I gave,
and searching for quot;45quot; would give 6 (1 2 3 0). Both of these searches would
return quot;error messagequot; using the last formula.

You have a couple options depending on how you want to distinguish the
strings. Using the example you gave, how would you clarify that quot;AA-BBquot; is
not included in quot;AAAA-BBBBquot;, but is included in something else like
quot;ZZAA-BB-YYquot;? Because the exact string is in both.

quot;clubinquot; wrote:

gt; I have an issue where I am trying to lookup certain text strings and return
gt; values corresponding to the strings from a separate worksheet. However, the
gt; strings do not match exactly on the worksheets. For example on the sheet
gt; with the values to lookup it will list
gt;
gt; quot;AAAA-BBBBquot;
gt;
gt; but on the data worksheet it will have something like
gt;
gt; quot;ZZAAAA-BBBB-YY
gt;
gt; The text string is there, but using vlookup will not work as they do not
gt; match exactly. In addition the lookup function doesn't seem to work as there
gt; may be other strings in the data sheet such as
gt;
gt; quot;AA-BBquot;
gt;
gt; which is returned from the lookup function. Is there a way to find the
gt; exact string within other strings as part of the array on the data sheet, or
gt; is this not possible?
gt;
gt; Please help.
gt;
gt; Thank you,
gt;
gt; Chaim

I didn't know you use the asterisk like that. You can use the VLOOKUP
function then can't you?
=VLOOKUP(quot;*c*quot;,B15:C:22,2,FALSE)

quot;Don Guillettquot; wrote:

gt; try using match with wildcard to find the row and then use in an index
gt; function.
gt;
gt; =INDEX(B15:C22,MATCH(quot;*c*quot;,B15:B22,0),2)
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;clubinquot; gt; wrote in message
gt; ...
gt; gt;I have an issue where I am trying to lookup certain text strings and return
gt; gt; values corresponding to the strings from a separate worksheet. However,
gt; gt; the
gt; gt; strings do not match exactly on the worksheets. For example on the sheet
gt; gt; with the values to lookup it will list
gt; gt;
gt; gt; quot;AAAA-BBBBquot;
gt; gt;
gt; gt; but on the data worksheet it will have something like
gt; gt;
gt; gt; quot;ZZAAAA-BBBB-YY
gt; gt;
gt; gt; The text string is there, but using vlookup will not work as they do not
gt; gt; match exactly. In addition the lookup function doesn't seem to work as
gt; gt; there
gt; gt; may be other strings in the data sheet such as
gt; gt;
gt; gt; quot;AA-BBquot;
gt; gt;
gt; gt; which is returned from the lookup function. Is there a way to find the
gt; gt; exact string within other strings as part of the array on the data sheet,
gt; gt; or
gt; gt; is this not possible?
gt; gt;
gt; gt; Please help.
gt; gt;
gt; gt; Thank you,
gt; gt;
gt; gt; Chaim
gt;
gt;
gt;


This worked but only if AAAA-BBBB appears once in your list. Otherwise,
it will return the sum of each instance that AAAA-BBBB appears.

=SUMPRODUCT(--(ISNUMBER(FIND(quot;AAAA-BBBBquot;,C10:C13))),--(D1013))

Column C is where your text within text is and D is the value to
return.

Cheers,

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=496637
A small modification.

The only advantage to using this instead of the VLOOKUP is if you have
multiple lookup values, you can drag this formula down the list and not
have to change the lookup criteria manually for each lookup value. It
is also not case sensitive. Use FIND instead of SEARCH if case is
important.

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,$E$1:$E$4))),--($F$1:$F$4))

A1 is your lookup value. E is where the value is located and F is your
return value if TRUE.

Again, this only works if your lookup values only appear once in your
range.

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=496637