+ Reply to Thread
Results 1 to 11 of 11

Index Match with multiple matched values

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    45

    Index Match with multiple matched values

    Ive been trying to figure this out ALL day! How do I do an Index_Match Formula where Column P has multiple values for the Index? See below

    Column P

    $650.00 (Row12)
    $800.00 (Row 24)

    My current formula
    =INDEX($P$4:$P$31,MATCH(AH12,$AH$4:$AH$31,0))

    The zero at the end of the above formula will only provide the value for the 1st

    How can I get it to do for both values

    Column AH Values are

    9 (Row12)
    17 (Row24)

    So in Column AI it should show

    Row 12 as 650.0
    Row 24 as 800.00

    Hope you can understand and can provide guidance.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,481

    Re: Index Match with multiple matched values

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Index Match with multiple matched values

    Try this array formula

    =IFERROR(INDEX($P$4:$P$31,SMALL(IF($AH$4:$AH$31=AH12,ROW($AH$4:$AH$31)-ROW(H$5)+1),ROWS($H$4:$H4))),"")

    ...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. Press F2 on that cell and try again.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Contributor meus's Avatar
    Join Date
    11-25-2014
    Location
    kathmandu
    MS-Off Ver
    2010/ 2013
    Posts
    287

    Re: Index Match with multiple matched values

    Hey AlKey,!! can i know what difference does Array formula and non formula makes??
    I wanted to know because i have a worksheet where using an array and non-arrary formula for same displays the same results/..
    Thanks

  5. #5
    Registered User
    Join Date
    09-13-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Index Match with multiple matched values

    I have attached the file in question and highlighted the columns that im trying to do the index match on.
    Attached Files Attached Files

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Index Match with multiple matched values

    I think this one will work for you

    in AI4 and copy down

    =IF(AND($AH$4:$AH$31>1,P4<>""),P4,"")

  7. #7
    Registered User
    Join Date
    09-13-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Index Match with multiple matched values

    Well its close but is it possible to get the value in Column P to show on in each cell where AH value is listed . For Example...

    Column AH

    9 (row 12)
    9 (row 16)

    I would like both to read $650 (which was the value in P12), is this possible?

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Index Match with multiple matched values

    I don't understand why, but I think it would be a good time now to explain what you really need. Otherwise, that would be just a guessing game.

  9. #9
    Registered User
    Join Date
    09-13-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Index Match with multiple matched values

    I added an updated workbook with exactly what I am trying to accomplish.

    I want to know if there is a value in Column P that the value is placed in Column AH for ALL that match. Hard to verbalize but the worksheet shows
    Attached Files Attached Files

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Index Match with multiple matched values

    Maybe this

    =IF(OR($AH$4:$AH$31={9,17}),MAX(P$4:P4),"")

  11. #11
    Registered User
    Join Date
    09-13-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Index Match with multiple matched values

    Actually, the value in AH will be different each time and that's why I thought a Index_Match would be the best. And actually the formula provided doesn't appear to be working correctly. Hmm.. I might be asking for the impossible and I know my explanation is sub standard. I have added the formula you suggested and have attached the results. You will notice the error.
    Attached Files Attached Files

+ 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. 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
  2. [SOLVED] Two criteria Index Match on multiple sheets returning multiple values
    By Joak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2014, 10:03 AM
  3. Replies: 4
    Last Post: 12-04-2012, 04:45 PM
  4. Replies: 2
    Last Post: 07-17-2012, 11:53 AM
  5. Index & Match multiple values
    By jmcgallan in forum Excel General
    Replies: 3
    Last Post: 11-02-2010, 06:33 AM

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