check first 3 characters of a cell and compare to a table
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|