Matching cells query

[Geezer34]

Hi guys

Having some issues here that I am hoping you can help me with.

I have 2 worksheets - main and match

In the main I have something like the following:

KeyID Company_name Address
123 ABC co 123 March Street
.....

There are 200 entries in the main work sheet.

In the match work sheet I have something like:

KeyID Company_name Address
123 ABC co 123 March Street

I need to be able to flag if the entry in the match work sheet is already in the main worksheet by using the unique keyID. This flag will be in the match worksheet.

I am using the following array formula:

{=IF(A3='[main.xls]Main File'!$A$8:$A$16,"y","n")} and was hoping that this would give me a Y in my flag column but this only shows a N.

If I just do a straight if statement then I get a yes:

=IF(A2='[main.xls]Main File'!$A$29,"y","n")

Any ideas what I need to do to get this to work????

Thanks

[NBVC]

tRY:

=IF(ISNUMBER(MATCH(A3,'[main.xls]Main File'!$A$8:$A$16,0)),"y","n")

[Geezer34]

That seems to do the trick :-)

Just for my reference can you tell me why the orig array formula did not work please.

Ta

[NBVC]

Your formula compares A3 only to the first cell in the range (i.e A8), even though it evaluates out each cell against A3 and gives proper TRUE/FALSE array, The If looks at the first result only and then evaluates which condition to apply from there (if the match to A3 was in A8, you would have gotten a "y"... the Isnumber(Match()) looks for a position in the range that a match is found... so that position is a number and so returns only one TRUE which the IF() statement evaluates.

Not that easy to explain.. but hopefully you get my drift...

[Geezer34]

Got it - thanks for the explanation