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!