two arrays
 

Hi,
In col A througn F there are numbers about 150 rows down.
In G1 through L1 there are numbers and these will change from time to time.
G2 through L2 and down I want a formula that will find the numbers that
are in A through F and put YES in the corresponding cell under G to L.
So any cell in A:F that has one of the numbers in G:L the formula will
put yes in the corresponding cell under G:L if the numbers is in G1:L1.


A B C D E F G H I J
K L
Numbers 3 5 12 16 34 27
38 40 48 52 56 46
34 32 33 43 53 9 Yes
8 15 16 24 38 19
13 15 16 32 41 22
23 36 27 41 42 21 Yes
11 27 32 48 52 28
5 7 12 16 45 19 Yes Yes
24 31 33 46 50 7
16 17 18 30 37 27 Yes Yes
2 13 16 37 44 32


Hope this makes sense.
Thanks,
Jerry

two arrays
 

Do you want the rows in A through F to match the first row in H through L?

If so, and I understand you correctly, try putting this formula in G2 and
copying it down and across: =IF(H$1=A2,"YES","")



"Jerry Kinder" wrote:

Hi,
In col A througn F there are numbers about 150 rows down.
In G1 through L1 there are numbers and these will change from time to time.
G2 through L2 and down I want a formula that will find the numbers that
are in A through F and put YES in the corresponding cell under G to L.
So any cell in A:F that has one of the numbers in G:L the formula will
put yes in the corresponding cell under G:L if the numbers is in G1:L1.


A B C D E F G H I J
K L
Numbers 3 5 12 16 34 27
38 40 48 52 56 46
34 32 33 43 53 9 Yes
8 15 16 24 38 19
13 15 16 32 41 22
23 36 27 41 42 21 Yes
11 27 32 48 52 28
5 7 12 16 45 19 Yes Yes
24 31 33 46 50 7
16 17 18 30 37 27 Yes Yes
2 13 16 37 44 32


Hope this makes sense.
Thanks,
Jerry

two arrays
 

In G2 copied across and down

=IF(ISNUMBER(MATCH(G$1,$A2:$F2,0)),"yes","")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=516554

two arrays
 

THANKS, it works great!
Jerry

"daddylonglegs"
wrote in message
news:daddylonglegs.23t1gz_1140903905.1237@excelfor um-nospam.com...

In G2 copied across and down

=IF(ISNUMBER(MATCH(G$1,$A2:$F2,0)),"yes","")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=516554

two arrays
 

Just to extend it a little further ..

If we want to count the number of numbers within cols A to F (in row2 down)
which match the reference set of numbers in G1:L1 (eg: as in checking lotto
tickets against results)

we could put in say, M2:
=SUMPRODUCT(--ISNUMBER(MATCH(A2:F2,G$1:L$1,0)))
and copy M2 down to return the counts

[The numbers in cols A to F, and those in the reference set
can be in any order, need not be sorted]
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---