+ Reply to Thread
Results 1 to 5 of 5

Formula/Function to Insert Price Given 3 Criteria- Multiple Possible Criteria Combinations

  1. 04-14-2017, 03:21 PM #1
    Yonnie03
    Yonnie03 is offline
    Registered User
    Join Date
    04-14-2017
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    2

    Formula/Function to Insert Price Given 3 Criteria- Multiple Possible Criteria Combinations

    I charge customers based on the equipment they use, the type of customer (internal, external, non-profit), and the time of day that they use the equipment (day or night/weekend rate). How can I populate column F on the Billing tab based on these three criteria and the prices on the Price List tab? This could be a combination IF/AND formula, but there are so many combinations of equipment/user/rate type (I'm only showing 4 pieces of equipment in my example, but there are many more), the formula would be incredibly long and complex. There has to be a better way! Thanks in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-14-2017, 03:32 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,457

    Re: Formula/Function to Insert Price Given 3 Criteria- Multiple Possible Criteria Combinat

    Is "Equipment A" the same as "Type A"?

    You could use an Array Formula in a VLOOKUP or INDEX/MATCH formula but that could affect performance. What would be easier is to combine the key fields on the Price List in a Helper column. Then you can combine the criteria on the Billing sheet to facilitate a simple VLOOKUP.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 04-14-2017, 03:37 PM #3
    avk
    avk is offline
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,223

    Re: Formula/Function to Insert Price Given 3 Criteria- Multiple Possible Criteria Combinat

    Refer attach file.
    Firstly confirm regarding "Equipment" : In "billing" you mentioned Type A whereas in sheet "pricelist" mentioned Equipment A. Hence i change Type A to Equipment A.
    In both sheet i add helper column (This is for easy calculation)
    In column "F2"
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    copy paste down.
    This is array formula hence need to Hold [SHIFT+CTRL+ENTER] while copy paste formula.
    Attached Files Attached Files
    Last edited by avk; 04-14-2017 at 03:37 PM. Reason: File Not attach

    atul

    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
    Reply With QuoteRegister To Reply
  4. 04-14-2017, 04:12 PM #4
    Yonnie03
    Yonnie03 is offline
    Registered User
    Join Date
    04-14-2017
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    2

    Re: Formula/Function to Insert Price Given 3 Criteria- Multiple Possible Criteria Combinat

    Yes, Equipment A and Type A are the same thing. Sorry about that! The Helper Column is great. I don't know why I've never thought of that before. I've never used an array formula before so I will have to read up, but this looks like it will work!

    Thanks!
    Reply With QuoteRegister To Reply
  5. 04-14-2017, 05:14 PM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,457

    Re: Formula/Function to Insert Price Given 3 Criteria- Multiple Possible Criteria Combinat

    You don't need a Helper column on the Billing sheet. You can just use:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    It is NOT, at least, it DOES NOT NEED TO BE, an Array Formula.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to get total price with multiple criteria based on (ORDER NUMBER) and (DATE)?
    By yaseralhosani in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-06-2016, 11:20 PM
  2. Formula to calculate a price from multiple criteria
    By orshims in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-18-2013, 04:11 PM
  3. [SOLVED] If any combinations of multiple criteria met, then find average time elapsed
    By Verdant in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 01:57 AM
  4. Price Value based on multiple criteria
    By Brandbrella in forum Excel General
    Replies: 1
    Last Post: 09-17-2012, 02:42 PM
  5. Return Price Based on Multiple Criteria
    By tigabalm in forum Excel General
    Replies: 2
    Last Post: 06-08-2011, 12:05 AM
  6. Excel 2007 : Sumifs formula lookup multiple criteria in criteria range
    By matthews311 in forum Excel General
    Replies: 1
    Last Post: 05-16-2011, 05:00 PM
  7. Finding lowest cost price with multiple criteria
    By jimbokeep in forum Excel General
    Replies: 8
    Last Post: 06-30-2010, 01:11 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1