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

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

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

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