# Thread: MATCH in a Two Dimensional Array (Excel 2000 SP3)

1. ## MATCH in a Two Dimensional Array (Excel 2000 SP3)

Is it possible to use the MATCH command to locate a value in a two dimensional array/table? Is there a better way besides MATCH? The attached workbook demonstrates what I'm trying to do.

N.B. I know how to work around this issue, but it's just one of those challenges that you set yourself now and then that get the better of you when you can't make it work as you think it ought to... Sigh... ;-)

Cheers
Peter

2. ## Re: MATCH in a Two Dimensional Array (Excel 2000 SP3)

Your formula does not give an answer since Match looks up in 1 column not all of them.

If you are only looking to see if there are any trues then use:
=COUNTIF(G3:J6,TRUE)=0

It will give TRUE if no trues are found and if any trues are found will give FALSE

If you are only looking for the address of an answer you can use something like this:

=IF(COUNTIF(G3:J6,TRUE)=0,"No Match",IF(COUNTIF(G3:J6,TRUE)>1, "Multiple Answers",ADDRESS(SUM(IF(G3:J6=TRUE,ROW(G3:J6))),SU M(IF(G3:J6=TRUE,COLUMN(G3:J6))))))

If there are no trues you get "No Match", if 1 true you get the address, if more than 1 you get "Multiple Answers"

If you are looking for something else you will have to elaborate
Steve

3. ## Re: MATCH in a Two Dimensional Array (Excel 2000 S

Thanks Steve... Your first suggestion does exactly what I need it to do.

Cheers
Peter

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•