+ Reply to Thread
Results 1 to 4 of 4

How to return multiple values from match the column number of the matched values.

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    How to return multiple values from match the column number of the matched values.

    Please see attached example.

    Please note that I do not want to exchange the solution with SUMPRODUCT or SUMIF. I just want to learn why the hardcoded values inside my formula could not be replaced with Match function.

    I just want to use Match function here to MATCH(D13,B1:L1,0) to return {2,5,7}.

    I also tried using =--isnumber(MATCH(B1:L1,D13,0)) which will return '={0,1,0,0,1,0,1,0,0,0,0}
    So i do not know how to get the positions of 1 1 1 from result of {0,1,0,0,1,0,1,0,0,0,0} and convert them into {2,5,7}

    Thanks for your help.
    Attached Files Attached Files
    Last edited by Flora20; 10-11-2020 at 06:42 PM. Reason: added details

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to return multiple values from match the column number of the matched values.

    Because as you may have noticed the MATCH function only returns the first occurrence of a match. Hence your D18 formula find the 345 in C4

    Obviously a SUMIF($B$1:$L$1,$D$13,$B4:$L4) is much neater
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    Re: How to return multiple values from match the column number of the matched values.

    Thanks Richard, I could do it with SUMIFs, but i was wondering why cannot I make it work with Match.

  4. #4
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    Re: How to return multiple values from match the column number of the matched values.

    Thanks Richard. I marked this to solved.

+ 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: 1
    Last Post: 09-04-2017, 02:11 PM
  2. return multiple values based on a repeating number, Index and Match?
    By damagedbodies in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-28-2017, 10:42 AM
  3. [SOLVED] Match Values In Two Sheets And Return Value At The Top Of The Column It Was Matched In
    By dylanvv in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 08-17-2015, 09:27 AM
  4. Replies: 9
    Last Post: 01-17-2015, 09:06 AM
  5. Index Match with multiple matched values
    By lalahaedong_excel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-18-2014, 11:57 AM
  6. Replies: 4
    Last Post: 12-04-2012, 04:45 PM
  7. Lookup cell value in separate worksheet and return multiple matched values
    By jwhite68 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2007, 12:17 PM

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