+ Reply to Thread
Results 1 to 4 of 4

return multiple values based on a repeating number, Index and Match?

  1. #1
    Registered User
    Join Date
    03-08-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    24

    return multiple values based on a repeating number, Index and Match?

    If you look at the attached spread sheet i need to make a formula that will look at 1 column for a number and return the 1st column value to the right and the 2nd column value to the right. or just the first one and i can figure out how to make it return the 2nd column.
    So far i did this on my main excel book which i didnt upload. i uploaded a simpler version.
    =IF(ISERROR(INDEX($A$3:$B$788,SMALL(IF($A$3:$A$788=$K$2, ROW($A$3:$A$788)),ROW(1:1)),2)),"",INDEX($A$3:$B$788,SMALL(IF($A$3:$A$788=$K$2, ROW($A$3:$A$788)),ROW(1:1)),2))
    But it screws up some times by returning values that are not part of my original number "K2" i am looking up.
    Attached Files Attached Files

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

    Re: return multiple values based on a repeating number, Index and Match?

    Try this formula in F2 copied right and down

    =IFERROR(INDEX(B$2:B$5, AGGREGATE(15,6, (ROW($B$2:$B$5)-ROW($B$2)+1)/($A$2:$A$5=$E$2),ROWS($F$2:$F2))),"")
    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

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: return multiple values based on a repeating number, Index and Match?

    or array entered
    =IFERROR(INDEX(B$2:B$10,SMALL(IF($A$2:$A$10=$E$2,ROW($A$2:$A$10)),ROW(A1))-1),"") (across and down)
    but after that you will need change format of cells to date and number accordingly
    Last edited by sandy666; 04-26-2017 at 11:57 AM.

  4. #4
    Registered User
    Join Date
    03-08-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: return multiple values based on a repeating number, Index and Match?

    =IFERROR(INDEX(B$2:B$5, AGGREGATE(15,6, (ROW($B$2:$B$5)-ROW($B$2)+1)/($A$2:$A$5=$E$2),ROWS($F$2:$F2))),"") worked great!
    Thanks a bunch,
    Now how about this one.
    Lets say i want to return the value in column G if it is in the month of October and if there are multiple values for October i need them added up. So on this one it would add up to 22500 since there is only one entry for October.

+ 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. Returning multiple distinct/repeating values for vlookup or index-match
    By amatvien in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-21-2018, 01:14 PM
  2. [SOLVED] Match Index to return multiple values
    By ed4ed2ed in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2015, 05:55 PM
  3. Using Index/Match to return multiple values for one match
    By superboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2014, 06:21 PM
  4. Match index from multiple spreadsheets-Return sum of values
    By Bravo33 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2013, 02:03 PM
  5. Need to look up a value and return multiple values-INDEX/MATCH?
    By abigail99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-03-2012, 10:05 AM
  6. Replies: 2
    Last Post: 07-17-2012, 11:53 AM
  7. [SOLVED] How to use Index Match to return multiple values
    By pingpoeng in forum Excel General
    Replies: 2
    Last Post: 04-09-2012, 09:58 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