# Thread: MATCH Function (OXP-SP2; WinME)

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

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

5. ## 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.

6. ## Re: MATCH Function (OXP-SP2; WinME)

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

7. ## 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
•