Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can EXACT search for text in an entire spreadsheet?
Hi
I'm working from Excel mac 2008 and I am trying to create a formula that allows me to search a name from one spreadsheet, againt the entire contents of another spreadsheet with the result showing on a seperate spreadsheet (all within the same workbook)? I have tried to use Exact, but failed miserably? can anyone help suggest a way of doing this? Many thanks Maria |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can EXACT search for text in an entire spreadsheet?
Hi Maria
Try the below..Adjust the range as required....Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" With your search value in A1 of Sheet1; the below formula will return a match or no match after checking Sheet2 . Try this formula in B1 =IF(MIN(IF(Sheet2!A1:Z100=A1,COLUMN(A:Z)))0,"Matc h found","No match") 'For an exact match (case sensitive) =IF(MIN(IF(EXACT(Sheet2!A1:Z100,A1),COLUMN(A:Z))) 0,"Match found","No match") If this post helps click Yes --------------- Jacob Skaria "stuckonexcel" wrote: Hi I'm working from Excel mac 2008 and I am trying to create a formula that allows me to search a name from one spreadsheet, againt the entire contents of another spreadsheet with the result showing on a seperate spreadsheet (all within the same workbook)? I have tried to use Exact, but failed miserably? can anyone help suggest a way of doing this? Many thanks Maria |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can EXACT search for text in an entire spreadsheet?
Hi Jacob,
Thank you so much for your reply, I'm afraid my excel knowledge is so basic that I am struggling to get the formula you provided me to work? I feel rather sheepish but is there any chance you could go through how to prepare the formula step by step? Kind regards Maria "Jacob Skaria" wrote: Hi Maria Try the below..Adjust the range as required....Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" With your search value in A1 of Sheet1; the below formula will return a match or no match after checking Sheet2 . Try this formula in B1 =IF(MIN(IF(Sheet2!A1:Z100=A1,COLUMN(A:Z)))0,"Matc h found","No match") 'For an exact match (case sensitive) =IF(MIN(IF(EXACT(Sheet2!A1:Z100,A1),COLUMN(A:Z))) 0,"Match found","No match") If this post helps click Yes --------------- Jacob Skaria "stuckonexcel" wrote: Hi I'm working from Excel mac 2008 and I am trying to create a formula that allows me to search a name from one spreadsheet, againt the entire contents of another spreadsheet with the result showing on a seperate spreadsheet (all within the same workbook)? I have tried to use Exact, but failed miserably? can anyone help suggest a way of doing this? Many thanks Maria |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can EXACT search for text in an entire spreadsheet?
Copy/paste whichever of Jacob's formula you want to use into the Formula Bar
(*not* in the cell) on your Excel worksheet and then press Ctrl+Shift+Enter (or whatever the Mac equivalent of those keys are... I think the Ctrl key is your Command key) at the same time. When you do that, Excel will place curly braces {} around the formula (you cannot type the curly braces in... you must let Excel do it and it will do it when you press those three keys simultaneously). -- Rick (MVP - Excel) "stuckonexcel" wrote in message ... Hi Jacob, Thank you so much for your reply, I'm afraid my excel knowledge is so basic that I am struggling to get the formula you provided me to work? I feel rather sheepish but is there any chance you could go through how to prepare the formula step by step? Kind regards Maria "Jacob Skaria" wrote: Hi Maria Try the below..Adjust the range as required....Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" With your search value in A1 of Sheet1; the below formula will return a match or no match after checking Sheet2 . Try this formula in B1 =IF(MIN(IF(Sheet2!A1:Z100=A1,COLUMN(A:Z)))0,"Matc h found","No match") 'For an exact match (case sensitive) =IF(MIN(IF(EXACT(Sheet2!A1:Z100,A1),COLUMN(A:Z))) 0,"Match found","No match") If this post helps click Yes --------------- Jacob Skaria "stuckonexcel" wrote: Hi I'm working from Excel mac 2008 and I am trying to create a formula that allows me to search a name from one spreadsheet, againt the entire contents of another spreadsheet with the result showing on a seperate spreadsheet (all within the same workbook)? I have tried to use Exact, but failed miserably? can anyone help suggest a way of doing this? Many thanks Maria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine: ISNUMBER(SEARCH( and EXACT( ? | Excel Discussion (Misc queries) | |||
How to Restrict Forum Search to Exact Term | Excel Discussion (Misc queries) | |||
search word or text in excel spreadsheet | Excel Discussion (Misc queries) | |||
a exact string search inquiry | Excel Worksheet Functions | |||
Search Entire Workbook | Excel Discussion (Misc queries) |