Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
check first 3 characters of a cell and compare to a table
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
check first 3 characters of a cell and compare to a table
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
check first 3 characters of a cell and compare to a table
Claus Busch wrote:
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
check first 3 characters of a cell and compare to a table
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to check first 5 characters of a cell & then sum | Excel Worksheet Functions | |||
Compare One Cell to the First 10 Characters of Another Cell | Excel Worksheet Functions | |||
Check cell address for double alpha characters | Excel Programming | |||
compare strings - highlight characters which are different | Excel Programming | |||
Compare first characters in two cells | Excel Programming |