+ Reply to Thread
Results 1 to 13 of 13

Finding a specific set of numbers in an array of numerical data

  1. #1
    Registered User
    Join Date
    06-22-2019
    Location
    Indiana
    MS-Off Ver
    1152.18.1903.0
    Posts
    4

    Finding a specific set of numbers in an array of numerical data

    Normally I'm somewhat adept at creating decent Excel formulas to meet my needs. However, this particular formula and its requirements have me stumped.

    So I'm turning to all you Excel wizards out there for help...

    I'm searching for a formula that will allow me to take 5 numbers in a single column...all in different cells, for instance:

    1
    2
    3
    4
    5

    and look for a specific match in an array of data on a separate spreadsheet within the same workbook, where the numbers go across:

    1 2 3 4 5

    The numbers in the second spreadsheet are preceded by a date, i.e. 1/1/1900.

    So it looks like this:

    1/1/1900 1 2 3 4 5

    with rows and rows and rows of data.

    If there is an exact match, I'd like only the date returned.

    I've tried using MATCH, INDEX, COUNTIF, etc...but haven't been able to put my finger on exactly what I need.

    Any help would be very much appreciated. Thanks in advance.

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Finding a specific set of numbers in an array of numerical data

    Hi and welcome

    I used this formula in the attached example to test values in column A against a few strings in column D in accordance with the format you showed

    Please Login or Register  to view this content.
    attached might help explain it
    Happy with my advice? Click on the * reputation button below

  3. #3
    Registered User
    Join Date
    06-22-2019
    Location
    Indiana
    MS-Off Ver
    1152.18.1903.0
    Posts
    4

    Re: Finding a specific set of numbers in an array of numerical data

    Thanks for your quick reply....much appreciated.

    The data in the second spreadsheet is all in its own cell.....so the date has its own cell, as does each of the 5 numbers.

    Any thoughts about how we can approach this formula, knowing that each data point has its own cell?

    Thanks again.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Finding a specific set of numbers in an array of numerical data

    It is best if you will upload a representative sample Excel workbook.

    If you are not familiar with how to do this (the 'paperclip' icon doesn't work) do this:

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.
    Dave

  5. #5
    Registered User
    Join Date
    06-22-2019
    Location
    Indiana
    MS-Off Ver
    1152.18.1903.0
    Posts
    4

    Re: Finding a specific set of numbers in an array of numerical data

    Here is an example of what I'm working with.

    So I want to check the numbers in green on the RESULTS spreadsheet against the DATA spreadsheet to see if there is any particular date (ROW) where those exact numbers show up.

    If you have any more questions, or need more clarity, please let me know.

    Thanks again!
    Attached Files Attached Files
    Last edited by sameltopia; 06-24-2019 at 01:46 PM.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Finding a specific set of numbers in an array of numerical data

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  7. #7
    Registered User
    Join Date
    06-14-2019
    Location
    Charlotte, NC, USA
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Finding a specific set of numbers in an array of numerical data

    Here's a possible solution using an array formula, if you have a version of Excel that can use the Concat() function.

    Also, are you able to add a column to your data table for reference?

    See attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-14-2019
    Location
    Charlotte, NC, USA
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Finding a specific set of numbers in an array of numerical data

    Actually Fluff13 has a pretty good solution....

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Finding a specific set of numbers in an array of numerical data

    This would be a better way, as it accounts for 2 digit numbers better
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Finding a specific set of numbers in an array of numerical data

    Please try
    =LOOKUP(1,0/(MMULT(N(TRANSPOSE(F3:F7)=DATA!B1:F20),{1;1;1;1;1})=5),DATA!A1:A20)
    and press Ctrl+Shift+Enter

  11. #11
    Registered User
    Join Date
    06-22-2019
    Location
    Indiana
    MS-Off Ver
    1152.18.1903.0
    Posts
    4

    Re: Finding a specific set of numbers in an array of numerical data

    My Dear Excel Wizards,

    I am pleased to report that your formula ideas worked perfectly.

    I cannot express just how much I appreciate your help with this. I am forever grateful for your time and wisdom

    Cheers,

    Sameltopia

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Finding a specific set of numbers in an array of numerical data

    Dear Sameltopia,

    Thank you for the feedback, I'm very happy that the formula helps you.

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Finding a specific set of numbers in an array of numerical data

    Glad we could help & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula for finding averages based on numerical data
    By olsebk01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2017, 04:06 PM
  2. [SOLVED] Finding the number of times a specific numerical day occurs
    By fearonc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2015, 01:07 PM
  3. Finding a specific sum from range of numbers
    By Togaman7 in forum Excel General
    Replies: 5
    Last Post: 08-31-2015, 08:39 PM
  4. Find and extract specific text data & accompanying numerical data
    By gabrielemucho in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 06-18-2013, 05:58 PM
  5. [SOLVED] Array for Finding Prime numbers in a cell
    By Karnik in forum Excel General
    Replies: 6
    Last Post: 11-11-2012, 08:54 PM
  6. Finding a Specific Sum in a List of Numbers
    By pugsly8422 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-03-2012, 10:25 AM
  7. Finding a specific word in a cell and executing an array
    By jgray152 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-10-2010, 07:49 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1