MATCH against 65534 rows
 

Given the formula
=IF(ISNUMBER(MATCH(AN2,$AG$2:$AG$65534,0)),"Matche d","Unmatched")

is there a way to have MATCH test a similar range on another sheet? Or am I
stuck doing this formula on two separate sheets?

Yes, this type of thing can be done in Access, but it would be so much
cooler if I didn't have to do that. And, no, installing XL 07 on this
computer is not an option.

Dave
--
Brevity is the soul of wit.

MATCH against 65534 rows
 

=IF(ISNUMBER(MATCH(AN2,$AG$2:$AG$65534,0)),"Matche d
Sht1",IF(ISNUMBER(MATCH(Sht2!AN2,Sht2!$AG$2:$AG$65 534,0)),"Matched
Sht2","Unmatched"))

Returns a match on first found value only.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Dave F" wrote in message
...
Given the formula
=IF(ISNUMBER(MATCH(AN2,$AG$2:$AG$65534,0)),"Matche d","Unmatched")

is there a way to have MATCH test a similar range on another sheet? Or am
I
stuck doing this formula on two separate sheets?

Yes, this type of thing can be done in Access, but it would be so much
cooler if I didn't have to do that. And, no, installing XL 07 on this
computer is not an option.

Dave
--
Brevity is the soul of wit.

MATCH against 65534 rows
 

It just occurred to me that I need only do a nested if statement here.

Problem solved.

Duh, should have been obvious!
--
Brevity is the soul of wit.


"Dave F" wrote:

Given the formula
=IF(ISNUMBER(MATCH(AN2,$AG$2:$AG$65534,0)),"Matche d","Unmatched")

is there a way to have MATCH test a similar range on another sheet? Or am I
stuck doing this formula on two separate sheets?

Yes, this type of thing can be done in Access, but it would be so much
cooler if I didn't have to do that. And, no, installing XL 07 on this
computer is not an option.

Dave
--
Brevity is the soul of wit.

MATCH against 65534 rows
 

Right, thanks, I just realized this!
--
Brevity is the soul of wit.


"Ken Wright" wrote:

=IF(ISNUMBER(MATCH(AN2,$AG$2:$AG$65534,0)),"Matche d
Sht1",IF(ISNUMBER(MATCH(Sht2!AN2,Sht2!$AG$2:$AG$65 534,0)),"Matched
Sht2","Unmatched"))

Returns a match on first found value only.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------



"Dave F" wrote in message
...
Given the formula
=IF(ISNUMBER(MATCH(AN2,$AG$2:$AG$65534,0)),"Matche d","Unmatched")

is there a way to have MATCH test a similar range on another sheet? Or am
I
stuck doing this formula on two separate sheets?

Yes, this type of thing can be done in Access, but it would be so much
cooler if I didn't have to do that. And, no, installing XL 07 on this
computer is not an option.

Dave
--
Brevity is the soul of wit.