can EXACT search for text in an entire spreadsheet?

  • Thread starter Thread starter stuckonexcel
  • Start date Start date
S

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