+ Reply to Thread
Results 1 to 3 of 3

Index/Match and return adjacent cell or workaround

  1. #1
    Registered User
    Join Date
    03-01-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    15

    Index/Match and return adjacent cell or workaround

    Hi folks, Can anyone help me with this little cost sheet problem?

    I have an index/match formula working that returns the ‘price’ after index matching ‘supplier’ and ‘term’ on another worksheet holding my rate card. I want to use the same formula or similar (or a workaround) in the adjacent cell so that the ‘per’ and ‘min hire’ fields are automatically populated as per the rate card.

    Is there a way to get the index match to look up a value and then return the value in the adjacent cell? Or the adjacent cell +1? In theory it should really index the term and supplier and then return 'price', 'per' or 'min hire' depending on my formula. Looking up adjacent cells doesn't really allow me to expand the rate card over time. Rather, it's just a little clunky.

    I know it is possible to adjust the range of the index to search only certain parts of the table but this doesn't solve my problem as I need the values in the price, per and min fields to update whenever I change/update terms or suppliers via the dropdowns i.e. the index must always search the data range in its entirety.

    I have my cost sheet and rate card on two separate tabs. Please see attached example.

    NB – Suppliers and terms are ‘Named’ in my working sheet so they appear in list form in the data validation drop downs.

    Also - cell refs might be slightly out since I have culled the example from my working sheet.

    Please help IT'S DRIVING ME BATTY!!!!!

    Thanks,
    jim
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index/Match and return adjacent cell or workaround

    Add +1 to the COLUMN() argument of the INDEX() formula.

    e.g

    =INDEX('Rate Card'!B4:J5,MATCH(A3,Suppliers,0),MATCH(B3,Terms,0)+1)

    and +2 to get the next column

    =INDEX('Rate Card'!B4:J5,MATCH(A3,Suppliers,0),MATCH(B3,Terms,0)+2)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-01-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Index/Match and return adjacent cell or workaround

    Ah yes - seems so obvious now. Thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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