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