+ Reply to Thread
Results 1 to 4 of 4

Find Cells that Match in a row in a table of numbers

  1. #1
    Registered User
    Join Date
    04-12-2019
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    64

    Find Cells that Match in a row in a table of numbers

    Hello,

    I'm trying to find a function or vba code that will help me find a certain string of numbers in a full table of numbers.

    Example: I want to be able to have 4 cells in a row like below.
    5 5 10 15

    and I want to be able to find the 4 cells in that row above that are in the table below that will return the first number.

    #1 3 3 9 16
    #2 5 5 10 15
    #3 5 6 7 8
    #4 5 10 15 20

    I want to be able to find the cells 5 5 10 15 in the table and bring back "#2" in another cell.

    I'm not sure if writing a VBA code would be easier or if there is a function that I could use to find the information.

    Thank you

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: Find Cells that Match in a row in a table of numbers

    can do it with this array formula =INDEX($A$3:$A$6,MATCH(B2&C2&D2&E2,B3:B6&C3:C6&D3:D6&E3:E6,0))
    remember, it is an array so it has to be activated using ctrl+shift+enter to activate it so that {} appear on each end of it, you cannot add those.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Find Cells that Match in a row in a table of numbers

    Easiest way (based on data given IMHO) would be to use CONCAT() in Column F for each row and then, with your values to match in B1:E1, use

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

    You can hide Column F. Does that work for you?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    04-12-2019
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    64

    Re: Find Cells that Match in a row in a table of numbers

    That worked perfectly. Thank you so much. Always forget that you can add multiple things to an argument.

+ 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. Replies: 3
    Last Post: 08-28-2019, 10:52 PM
  2. [SOLVED] Match not finding a match in a table when headers are numbers.
    By pxtaylor in forum Excel General
    Replies: 9
    Last Post: 06-07-2019, 04:08 PM
  3. using excel VBA find and higlight in red all prime numbers in a table of numbers
    By TeraFumba in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2017, 07:30 AM
  4. Reverse Index Match -- Find column and row numbers for a table
    By mlukich in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2016, 02:16 AM
  5. [SOLVED] Vlookup to Pivot Table, using Match function, returns error if can't find match value
    By AndrewHowarth in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2015, 12:10 AM
  6. [SOLVED] Application.Match help. Find a match if first 4 numbers are the same. Asterisk not working
    By thelisa in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-24-2013, 08:01 AM
  7. How to find MATCH numbers ?
    By toyota58 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-20-2005, 05:05 AM

Tags for this Thread

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