Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •