It seems to work for me with either text or numeric data (or a combination of
the two). Are you searching an entire column of data? Sumproduct can only
handle 65535 entries (most excel spreadsheets contain 65536). Are you
getting an error or an improper result?

The last formula I posted would give you the index number of the first
match. It could be combined with the Index function to return data in a
different column.


"JN" wrote:

Thanks! Does the data from both tables have to be in the same format in order
for the formulas to work?

I tried using the first formula
=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))))0

on a large set of numeric data. But it doesn't seem to work. For example,
Table A has "2077", "3145", "2677" in column N;
Table B has "10000002077", "100000003145", "100000002677".

I want to find whether "2077" from table A exists in Table B. Since the
number in table B has so many digits, I am interested in the last 4 to 5
digits. If "2077" exists in table b, then I want it to show certain info in
table B. I assume this part will have to be a vLookup, is this right? Thanks.




"JMB" wrote:

If TableB was in A1:A7 and the substring you were looking for was in cell B1,
one possibility could be:

=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))))0

If you wanted the index number of your match (assuming there's only one
match):

=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A7,1))),ROW(INDIRECT("1:"&R OWS(A1:A7))))0

If you have multiple matches, you can find the index of the first match
(this formula must be entered with Control+Shift+Enter):
=MIN(IF(--(ISNUMBER(SEARCH(D1,A1:A7,1)))=0,"",(--(ISNUMBER(SEARCH(D1,A1:A7,1)))*ROW(INDIRECT("1:"&R OWS(A1:A7))))))



"JN" wrote:

I am not trying to add any characters here. I am trying to match up what in
Table A with the master data in Table B. The problem is that the data in
Table B contains long string of text, while table A only contains a portion
of this string.

For instance, in Table A, I have "1A889"; then Table B has "ST01A889000,
ST01A99900... etc." I want to find if Table B has this string "1A889".

Thx.


"Ken Wright" wrote:

Hi Chuck - If he's adding ST to Table A items he needs to lose the leading
0s first, else they won't match.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"CLR" wrote in message
...
Using Edit Replace. or the LEFT feature, etc.........you can either
delete
the ST from table B items, or add it to table A items......

hth
Vaya con Dios,
Chuck, CABGx3


"JN" wrote:

Hi,

I am trying to match up a list of accounts (table A) from a huge text
table
(table B). The problem is table A doesn't have the same format as the
text
strings in table B. The text in table A contains part of the text strings
in
table B.

For example,
Table A has 01A0000
Table B has ST1A0000

Thx!