Match formula

B

bob

hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance
 
B

bob

thanks Biff
ok i want to count the number of times a matching number appears in
row1 from a group of numbers in row2 so i would expect the result of
your question to be "3". i can do a countif (a1,a2:e2) in cell W1 then
do a countif(b1,a2:e2) in cell X1etc.but i have a group of 8 cells to
try and match and was hoping there would be an better way to do it in
just one cell for all 8 cells

something like =countif (a1:e1, anymatchs a2:e2) but i know thats not
it
thanks in advance

bob
 
V

vezerid

I think you need something like:

=SUM(--(COUNTIF(A1:E1,A2:E2)<>0))

entered as array formula (i.e. confirmed with Shift+Ctrl+Enter). The
philosophy of this formula is that it goes over each element in row 2
and asks if the element appears at least once.

Is it what you want?
Kostis Vezerides
 
M

Max

Try also:
=SUMPRODUCT(--ISNUMBER(MATCH(A2:E2,A1:E1,0)))
Adapt to suit for an 8 cells stretch ..
 
B

bob

thanks much max works like a charm..for some reason the other two did
not but thanks to all for trying will perhaps use those as needs
require

bob
 
B

bob

Max
is there a way to change the font to red for any matchs found if your
formula returns a returns a value of 1 or more
 
M

Max

bob said:
.. is there a way to change the font to red
for any matchs found if your formula
returns a returns a value of 1 or more
Click to expand...

Use conditional formatting

Select A2:E2 (ensure A2 is active)
Format > Cond Format
Formula is: =ISNUMBER(MATCH(A2,$A$1:$E$1,0))
Format to taste (font tab > red & bold?) > OK out

Numbers in A2:E2 which match those within A1:E1
will be conditionally formatted
 
B

bob

Max
What do you mean by (ensure a2 is active) i should have also told you
that i am working with excel 2000. I went into conditional formatting
and did not see anywhere that i could install a formula it just says at
least, equal, more etc. Am i missing something. a BIG thanks for taking
the time to work with me and others

bob
 
M

Max

bob said:
.. What do you mean by (ensure a2 is active) i should have also told you
that i am working with excel 2000. I went into conditional formatting
and did not see anywhere that i could install a formula it just says at
least, equal, more etc. Am i missing something.
Click to expand...

Let's take it again said:
Select A2:E2 (ensure A2 is active)
Click to expand...

A2 will be active cell if we select the range A2:E2 by left-clicking on cell
A2, then sweep right to E2. If we select the range the other way, by
left-clicking on cell E2, then sweep left to A2, then E2 will be the active
cell. The active cell will be the one filled "white" in the selected range,
the rest of the cells will be filled "black".

Selecting the range properly will ensure that the conditional format formula
we're going to place below (which is framed for the active cell) will then
"propagate" correctly/relatively within each of the cells in the range when
we complete all the steps for the Conditional format
Format > Cond Format
Click to expand...
With the range selected properly, click Format > Conditional Formatting

In the Conditional Formatting dialog:
Under Condition 1, click the droplist (where you see "Cell Value Is")
and select "Formula Is:"
Then copy the formula above and paste into the box to the right of "Formula
Is:"
(After you copy the part "=..." from the post,
click inside the box, then press CTRL+V to paste)
Click the Format button > choose the Font tab
Then select red under "Color" and "Bold" for Font style, click OK
Click OK at the main dialog
 
B

bob

Max
oooook got the active part down <g> and did the conditional formatting
as you said works to a degree. Am i to asume it reads left to right but
not right to left? Reason i am asking is two of the cells that matched
were formatted red but one was not. The two that were red were from
numbers to the right of A2 the one that did not was to the left of AE2

Example
A1 6636 3133 6661 3611 1113 6316 3313 6116
A2 1336 3163 1113 6316 3611 1616 3311 6366
1113 and 6316 were formatted but the 3611 which is also a match did not
thanks again !!!

bob
 
B

bob

ops i found out why ..was going to use it on other cells so dumb me
took out the $ ..when i put it back in works like a charm......BIG
THANKS !!!

bob
 
B

bob

Max
i know i know gessssss this guy is turning into a pain in the b***<g>
anyway now that i or we have the A1 A2 format down pat is there perhaps
a way to add a second conditional format to A1 but this time go to a
group of cells which has been marked with a "x" in another cell. I am
trying to follow 2 groups of cells this way

something like
=ISNUMBER(MATCH(A2,$A$1:$E$1,0))+ISNUMBER(MATCH(A2,$A$22:$E$22)) IF H22
"X"
 
M

Max

bob said:
... now that i or we have the A1 A2 format down pat is there perhaps
a way to add a second conditional format to A1 but this time go to a
group of cells which has been marked with a "x" in another cell. I am
trying to follow 2 groups of cells this way, something like
=ISNUMBER(MATCH(A2,$A$1:$E$1,0))
+ISNUMBER(MATCH(A2,$A$22:$E$22)) IF H22 = "X"
Click to expand...

Here's one way ..

A sample construct is available at:
http://www.savefile.com/files/3175912
Complex Match Formula in Cond Formatting.xls

Putting it as the 2nd** condition (using 2 separate conditions 1 & 2):
(CF range is A2:E2)

Condition 1, Formula is: =ISNUMBER(MATCH(A2,$A$1:$E$1,0))
Format > font tab > red/bold

(above as before, no change)

Click "Add", to add 2nd condition

Condition 2, Formula is:
=SUMPRODUCT(--ISNUMBER(MATCH(A2,OFFSET($H$1,MATCH("X",$H:$H,0)-1,-7,,5),0)))
0
Click to expand...
Format > font tab > blue/bold (say)
OK out

**Equivalent to the structure:
=IF(Cond1,"red/bold",IF(Cond2,"blue/bold",""))
where Cond1 will be evaluated first before Cond2, going from left to right

Note that it's assumed col H will contain only a single "X" at any one time,
to indicate the range, eg: A22:E22 to be used as the MATCH lookup array (2nd
condition)

---

The other way (which returns different results), is if we say, wish to
include the additional check as a single condition (under Condition 1) using
OR:

Putting it as a single condition (under Condition 1) using OR:
(CF range is A2:E2)

Condition1, Formula is:
=OR(ISNUMBER(MATCH(A2,$A$1:$E$1,0)),
SUMPRODUCT(--ISNUMBER(MATCH(A2,
OFFSET($H$1,MATCH("X",$H:$H,0)-1,-7,,5),0)))>0)

Format > font tab > red/bold > OK out
 
B

bob

Wow unbelievable thanks.can I call you Mr. Excel? And would you mind if
I pass your name on to Bill Gates
 
M

Max

You're welcome, Bob !
Thanks for the feedback ..

(I'm just one of the many folks enjoying himself here <g>!)
 
Top