+ Reply to Thread
Results 1 to 10 of 10

Index Match Help please

  1. #1
    Registered User
    Join Date
    03-29-2017
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Index Match Help please

    I am trying to use index match to return a descriptive value from a range, I am either doing it wrong or using the wrong method for my needs.

    I have a sheet named SKU that holds the name of the client in Row 1 beneath the clients name is a list of SKU codes the next column holds the description of the SKU codes which is what I want to return the value of

    I have a sheet named PRINT NOTE with the name of the client in cell B1 and the name of the SKU in cell A20.

    If I use the following match reference =match($B$1,SKU!$C$1:$AR$1,0)
    B1 = Multipower
    B1 = BOA
    B1 = CCE_Monster

    Then it returns the correct values i.e.
    1 for Multipower
    3 for BOA
    7 for CCE Monster

    IndexMatach.JPG

    I now want to find the relevant SKU description.

    Exp 1
    On sheet “PrintNote”
    Cell B1 contains ‘CCE_Monster’
    Cell A20 contains ‘G9’
    Cell B20 contains the Index Match formula to return the description required

    I therefore want the value in J7 to be returned to B20 I appreciate that I have found it is in the 7th column and now I want to count down 7 rows and return the value 1 to the right, but I can’t get the formula right

    Exp 2
    On sheet “PrintNote”
    Cell B1 contains ‘Mars_Chillers’
    Cell A20 contains ‘DEIMOS’
    Cell B20 contains the Index Match formula to return the description required

    I therefore want the value in H5 to be returned to B20

    Any help would be much appreciated.

    Many thanks

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,722

    Re: Index Match Help please

    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 REPLY then GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,613

    Re: Index Match Help please

    Agree we need to see a sample file, but this is the syntax of INDEX/MATCH/MATCH...
    =index(data-range,row-to-use,column-to-use)

    when you include MATCH...
    =index(data-range,match(criteria,COLUMN-RANGE-containing-criteria,0),match(criteria,ROW-RANGE-containing-criteria,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    03-29-2017
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Re: Index Match Help please

    Thank you for your responses, i have attached an example as requested.

    What i am trying to achieve is to find the locate the cell in the row 1 of the SKU
    tab that relates to the value held in cell A5 of the PrintNoteTest tab. In this example this is 'ClientD'

    ClientD is a named range in the SKU tab =SKU!$I:$I

    I then want it to match this to the SKU value held in A20, in this case "VD1544" and return the value held
    in the next field to the right, similar to a vlookup.

    VD1544 is in the 11th row in the range ClientID.

    I have displayed the results that i need in 'PrintNoteResult' I am aware that my index match is wrong in
    my test tab and i probably need to use a count somewhere.

    I hope this is clearer

    Many thanks

    Pros
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-04-2004
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    186

    Re: Index Match Help please

    try in b20
    Please Login or Register  to view this content.
    and copy down
    Last edited by philaugust2004; 03-31-2017 at 07:49 AM.

  6. #6
    Registered User
    Join Date
    03-29-2017
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Re: Index Match Help please

    Hi Phil, thanks for the input, the issue is that 'ClientD' is a variable based on the value returned to cell A5, therefore if 'ClientE' was in A5 and SKU 'CE041332A' was enrered into A20 your formula would work.

  7. #7
    Forum Contributor
    Join Date
    08-04-2004
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    186

    Re: Index Match Help please

    Sory, my mistake.
    Try this instead
    Please Login or Register  to view this content.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,613

    Re: Index Match Help please

    Try this beast, copied down...
    =if(A20="","",INDEX(OFFSET(SKU!$C$1,0,MATCH($A$5,SKU!$C$1:$L$1,0),COUNTA(ClientA),1),MATCH($A20,OFFSET(SKU!$C$1,0,MATCH($A$5,SKU!$C$1:$L$1,0)-1,COUNTA(ClientA),1),0)),"")

  9. #9
    Registered User
    Join Date
    03-29-2017
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Re: Index Match Help please

    Quote Originally Posted by philaugust2004 View Post
    Sory, my mistake.
    Try this instead
    Please Login or Register  to view this content.
    This works perfectly, thanks. I have adapted the model to work with google sheets, now i'm going to try to understand what this actually is doing. Thanks again

  10. #10
    Registered User
    Join Date
    03-29-2017
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Re: Index Match Help please

    Quote Originally Posted by FDibbins View Post
    Try this beast, copied down...
    =if(A20="","",INDEX(OFFSET(SKU!$C$1,0,MATCH($A$5,SKU!$C$1:$L$1,0),COUNTA(ClientA),1),MATCH($A20,OFFSET(SKU!$C$1,0,MATCH($A$5,SKU!$C$1:$L$1,0)-1,COUNTA(ClientA),1),0)),"")
    Thanks for your input.

+ 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. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  2. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 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