Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    MATCH not working? (2000sp4)

    Trying to use match to look up sample numbers in a series of nxn grids in different sheets, and not having much luck. Always seems to return #N/A.

    Quite possible that none of the numbers in the list, apart from the last one, are in the lists, but the last number has been cut and pasted from one of the sheets and still all the MATCH functions return #N/A.

    What am I doing wrong?

    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH not working? (2000sp4)

    Post deleted by shades

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH not working? (2000sp4)

    Match only works on a one column array. SInce it only returns a single number, there is no way for it to return a row number and a column number from a two dimensional array. If you can't arrange your lookup data into a single column, then you will either have to write a User Defined Function to do the lookup and return whatever value you want (what do you want returned?), or you will have to code a complicated IF statement that looks in each column independently and then somehow calculates whatever return you want.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: MATCH not working? (2000sp4)

    Damn. Was afraid it might be something like that - I had thought that MATCH was more flexible than V or Hlookup, but obviously in different ways.

    The data is in the nxn array for a reason, and needs to stay that way.

    Looks like I'll have to incorporate some VBA finding. How annoying.

    Interestingly online help doesn't hint at the unidimensionality of MATCH:

    "Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array can be an array or an array reference."

    Thanks

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: MATCH not working? (2000sp4)

    Yet it was made with the function wizard and not hand edited!

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MATCH not working? (2000sp4)

    Hi Mark,
    What are you actually trying to return as a value? (if you just need to know if it's there, you can use COUNTIF)

    Edit:
    If you actually wanted the address of the matching cell, if any, then you could use something like this for Plate 4 and adapt for the other three:
    =IF(COUNTIF('plate4 grid'!$B$4:$M$23,Sheet4!$B2)=0,"",ADDRESS(SUMPRODU CT(('plate4 grid'!$B$4:$M$23=Sheet4!$B2)*ROW('plate4 grid'!$B$4:$M$23)),SUMPRODUCT(('plate4 grid'!$B$4:$M$23=Sheet4!$B2)*COLUMN('plate4 grid'!$B$4:$M$23))))

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: MATCH not working? (2000sp4)

    Why do I always need to be reminded of the existence of CountIf?!?!?!?!?

    Thanks very much, some modification of your suggestion did exactly what I needed.

Posting Permissions

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