can EXACT search for text in an entire spreadsheet?

[stuckonexcel]

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

 

[Jacob Skaria]

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,"Match 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

 

[stuckonexcel]

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

 

[Rick Rothstein]

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).