Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MATCH Function (OXP-SP2; WinME)

    In using the MATCH function, can the lookup-value be a range of values? I realize that the second argument is the lookup-array and is usually a range of values. I want to check each value in a range against each of the values in another range. I will use the ,0 argument in order to return a numeric value when there is a match or '#NA' if there is no match.

    For example, I want to write this expression: =SUM(IF(ISNA(MATCH(b2:g2,b4:g4,0))0,1)). The expected result is that if none of the values in the range B2:G2 matches any of the values in the range B4:G4, the value returned to the SUM function will be 0 because the ISNA function will return zeroes because there is no match in the MATCH function. For each match, ISNA will return the value '1' and SUM will then yield the number of matches that MATCH returned. Example:

    B2:G2 = 4 11 14 36 41 25
    B4:G4 = 2 5 20 27 30 6

    The expected result is '0' because there are no values that occur in both ranges.


    What's wrong with my logic? This formula is not yielding the expected result; I get the value '1' regardless of the values in either of the ranges.

    All suggestion greatly appreciated and gratefully received.

  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 Function (OXP-SP2; WinME)

    How about the array formula (confirm with ctrl-shift-enter):

    =SUM(IF(B2:G2=$B$4:$G$4,1,0))

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH Function (OXP-SP2; WinME)

    Thanks for the help, Steve.

    I tried your formula and it returned '#VALUE'. Is this supposed to count all matches?

    Thanks again.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH Function (OXP-SP2; WinME)

    =SUMPRODUCT(--ISNUMBER(MATCH(B2:G2,B4:G4,0)))
    Microsoft MVP - Excel

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH Function (OXP-SP2; WinME)

    Select the cell with the formula, hit F2. Then press control-shift-enter (as Steve mentioned).

    The formula is an array fomrula and you have to tell XL that is the case by pressing control-shift enter instead of just enter.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH Function (OXP-SP2; WinME)

    Thank you, Gentlemen! Where does one learn about array functions requiring CNTL/SHIFT/ENTER?

  7. #7
    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 Function (OXP-SP2; WinME)

    Here is a good primer from Chip Pearson.

    Steve

Posting Permissions

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