check first 3 characters of a cell and compare to a table

[MAS]

Hi all,

I am looking for help with a formula that will look up the first 3
characters in a cell, compare it too a table in a different workbook and
return the result of "check" if the 3 characters match an entry in the
table

 

[Claus Busch]

Hi,

Am Sat, 09 Mar 2013 07:56:42 GMT schrieb MAS:

I am looking for help with a formula that will look up the first 3
characters in a cell, compare it too a table in a different workbook and
return the result of "check" if the 3 characters match an entry in the
table

if you want to count the matches try:
=COUNT(SEARCH(LEFT(A1,3),[MyWorkbook.xlsx]Sheet1!$A$1:$A$100))

Regards
Claus Busch

 

[MAS]

Hi,

Am Sat, 09 Mar 2013 07:56:42 GMT schrieb MAS:

I am looking for help with a formula that will look up the first 3
characters in a cell, compare it too a table in a different workbook and
return the result of "check" if the 3 characters match an entry in the
table

if you want to count the matches try:
=COUNT(SEARCH(LEFT(A1,3),[MyWorkbook.xlsx]Sheet1!$A$1:$A$100))

Regards
Claus Busch

Hi Claus,

I am more looking at a formula that will check the first 3 characters in
column A (i.e. A1C if A1C-123 is in a cell), then lookup column B starting
at row 3 in a different workbook, and return either "check" if the 3
characters appear in column B in the different workbook or "no" if it
doesn't.

 

[Claus Busch]

Hi,

Am Sat, 09 Mar 2013 23:09:59 GMT schrieb MAS:

I am more looking at a formula that will check the first 3 characters in
column A (i.e. A1C if A1C-123 is in a cell), then lookup column B starting
at row 3 in a different workbook, and return either "check" if the 3
characters appear in column B in the different workbook or "no" if it
doesn't.

you will get the row numbers fpr matches:
=SMALL(IF(LEFT([Mappe1.xlsx]Tabelle1!$B$1:$B$100,3)="A1C",ROW($1:$100)),ROW(A1))
ans copy down


Regards
Claus Busch

 

[Claus Busch]

hi,

Am Sun, 10 Mar 2013 09:06:10 +0100 schrieb Claus Busch:

=SMALL(IF(LEFT([Mappe1.xlsx]Tabelle1!$B$1:$B$100,3)="A1C",ROW($1:$100)),ROW(A1))
ans copy down

better:
=IF(ROWS($1:1)>COUNTIF([Mappe1.xlsx]Tabelle1!$B$1:$B$100,"A1C*"),"no match",SMALL(IF(LEFT([Mappe1.xlsx]Tabelle1!$B$1:$B$100,3)="A1C",ROW($1:$100)),ROW(A1)))
and enter the array formula with CTRL+Shift+ Enter
and copy down.


Regards
Claus Busch