+ Reply to Thread
Results 1 to 6 of 6

Index/match pricing formula

  1. #1
    Registered User
    Join Date
    12-12-2016
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    2

    Index/match pricing formula

    Good afternoon,

    I wonder if anyone could help me. I am trying to 'simplify' a massive pricing spreadsheet with a more simple interface (see attached example).

    What I want to do is have an index match fuction that looks at where the location of the work is and then the time of day that they are to take place, as the cost differs.

    Could someone have a look at the quick example and woorkbook and give me an idea of the formula needed.

    Many thanks

    Nathan
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,567

    Re: Index/match pricing formula

    Welcome to Excel Forum.
    This proposed solution consolidates the three location tables into L2:O6, then uses the the following formula to get the Rate:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The product of A4 and B4 is the Total.
    Let us know if you have any questions.
    Attached Files Attached Files
    Last edited by JeteMc; 12-12-2016 at 11:43 AM.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: Index/match pricing formula

    try this in Cell B4

    =OFFSET(M1,MATCH(G2,L:L,0)+MATCH(G3,$L$3:$L$5,0)-1,0)

  4. #4
    Registered User
    Join Date
    12-12-2016
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    2

    Re: Index/match pricing formula

    Ecellent! Thank you both.

  5. #5
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: Index/match pricing formula

    you're welcome

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,567

    Re: Index/match pricing formula

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ 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] This formula works but I donīt understand why Index(Index) match
    By campelliann in forum Excel General
    Replies: 2
    Last Post: 01-25-2016, 05:55 PM
  2. [SOLVED] Index/Match Multiple Criteria: Item # & Volume Level Pricing Relative to Specific Volume
    By dilbert1865 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-15-2014, 09:28 AM
  3. Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??
    By Underling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 09:01 PM
  4. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  5. Moving Data with Index & Match for Pricing Sheet
    By Cameron12 in forum Excel General
    Replies: 9
    Last Post: 07-25-2012, 07:23 PM
  6. Index Match Index Formula work slow
    By avk in forum Excel General
    Replies: 9
    Last Post: 03-07-2012, 02:19 PM
  7. Replies: 5
    Last Post: 02-29-2012, 08:51 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