#1   Report Post  
Posted to microsoft.public.excel.newusers
Jerry Kinder
 
Posts: n/a
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.newusers
ChelseaWarren
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.newusers
daddylonglegs
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
Jerry Kinder
 
Posts: n/a
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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
---


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using CORREL with arrays containing null values rmellison Excel Discussion (Misc queries) 1 November 11th 05 04:02 PM
vba adding arrays Jeff Excel Discussion (Misc queries) 1 November 4th 05 03:50 PM
Problem with plotting a chart when using arrays as Values and Xvalues [email protected] Charts and Charting in Excel 3 August 19th 05 09:05 PM
Confused about arrays and ranges in functions Llurker Excel Worksheet Functions 0 July 7th 05 05:44 AM
Comparing Arrays TangentMemory Excel Discussion (Misc queries) 2 May 13th 05 05:06 PM


All times are GMT +1. The time now is 02:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"