none
two-criteria lookup RRS feed

  • Question

  • Have made numerous attempts, but no joy.
    See below, need to lookup both the city *and* fee --- which changes all the time --- and arrive at the appropriate service charge.
    The fees are not all exact numbers --- mostly they fall in between the "bookends".

    As exemplified by the lookup results, a fee of $225 in Vancouver [which is between the bookends of   $200-$300] would result in a $3.00 s/c; In Phoenix a $56.50 fee results in a $0.75 s/c; in Denver anything over $200 results in $4.00; and so on.

    Thanks in advance ...!

    LOOKUP RESULTS:
    City Fee Service Charge
    Vancouver $225.00 $3.00
    Phoenix $56.50 $1.25
    Denver $850.00 $4.00
    LOOKUP   TABLE:
    City Fee Service Charge
    Denver $1.00 $0.50
    Denver $50.00 $1.00
    Denver $100.00 $2.00
    Denver $150.00 $3.00
    Denver $200.00 $4.00
    Phoenix $1.00 $0.25
    Phoenix $30.00 $0.75
    Phoenix $60.00 $1.25
    Phoenix $120.00 $1.65
    Phoenix $150.00 $2.10
    Phoenix $200.00 $3.00
    Vancouver $1.00 $0.50
    Vancouver $100.00 $2.00
    Vancouver $200.00 $3.00
    Vancouver $300.00 $5.00

    Thursday, March 19, 2015 11:55 AM

Answers

  • Looking at how to implement some workarounds, ie putting the custom function into numerous workbooks and them look up to a master table, etc.

    The alternative is to place the UDF in an AddIn...

    You can also use this formula in cell B6 instead, it's similar to my code:
    =VLOOKUP(B5,OFFSET($C$17,MATCH(B4,$B$17:$B$31,0)-1,0,COUNTIF($B$17:$B$31,B4),2),2,TRUE)

    The lookup table can be placed anywhere, that doesn't matter. Also the input fields "City" and "Price" can be placed anywhere, in this points there is no difference between an UDF and a formula.

    The only precondition is that the cities must be in consecutive cells and the associated price must be in ascending order.

    Andreas.

    • Proposed as answer by Dogubob Tuesday, March 24, 2015 4:01 PM
    • Marked as answer by Mikf Saturday, March 28, 2015 5:24 PM
    Sunday, March 22, 2015 8:15 AM

All replies

  • I don't know how you're getting some of those numbers.  The logic makes no sense to me.  Anyway, I think the between will be like this.

    =SUMPRODUCT(--(A2:A50="Denver"),--(B2:B50>=150),--(B2:B50<=250),--((C2:C50)))

    Modify to suit your needs.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, March 19, 2015 2:36 PM
  • Thank you for the reply.

    The fees fluctuate between bookends always.

    And ... need multiple criteria solved at the same time for multiple cities.

    Think of the "old" LOOKUP formula, which easily solves this [so does vlookup] except if it was only a matter of looking up service charges on fees, *without* the addition of the City criteria.

    So what I'm looking for is something like MATCH(City ...) / LOOKUP(Value From Specific City, Fee Column, Service Charge Column).

    So that a ..

     - Fee in Denver of between $50 and $99.99 returns a service charge of $1.00.

     - Fee in Phoenix of between $150 and $199.99 returns a service charge of $2.10.

     - Fee in Vancouver $300 and greater returns a service charge of $5.00 .

    It is unfeasible to use your solution.  The example is limited to three cities for brevity, but in real-world it's more like three hundred [at least], all with different Fee and Service Charge criteria.

    Thank you again though.

    *** Note -- there is a typo in the example's Lookup Results;  per the table, a Fee of between $30 and $59.99 there does not return a Service Charge of $1.25, it's $0.75

     - Mik

    Thursday, March 19, 2015 3:23 PM
  • So this works but you may need to tweak it a bit.

    http://exceltactics.com/vlookup-multiple-criteria-using-index-match/

    To use this, you have to build a three column table containing

    City - Order Total (or whatever you base your fee on) - Fee

    Denver   0   .10

    Denver 1     .10

    Denver 3     .10

    Denver 4     .20

    Etc.

    Obviously the table will be wicked long but just keep going. It's just data so who cares if you chew up 1000 rows?

    Follow the directions at the URL and combine 'Index' and 'Match'.  If you do the fee table by whole numbers, you'll need to take the user input ($3.32 for example) and convert it to just 3 which returns 0.10. If you put in 3.75, round up to 4 and get 0.20.

    There may be something else clever you can do with rounding or other math, but I gots to go...

    Thursday, March 19, 2015 6:15 PM
  • Thank you.

    That *would* work, but putting in a one cent increase from $1 to $2500 for three hundred cities, and then keeping it properly updated with all the yearly adjustments, would prove prohibitive.

    Essentially need to find a formula that ...

    a) Isolates the criteria city within the table.

    b) Then performs a lookup on the Fee [between the "bookends"] and returns the correct service charge.

    Thanks again,

      - Mik

    Thursday, March 19, 2015 8:24 PM
  • Mik

    That's why I suggested the 'rounding' thing so you just need whole dollars and yes, I get that it's a lot of work. I spent a bit of time searching here and via Mr. Google and the Match/Index thing is about the only thing I've found that does what you want by formula. You may need to rethink the method and switch to a macro or a different tool (an actual db maybe?)

    I hope someone posts a formula that works for you; this is an interesting problem.

    Friday, March 20, 2015 10:06 AM
  • Thanks for the msg.

    Actually did succeed in one manner --- put each city on separate sheets, then used Lookup(INDIRECT to accomplish the task.

    That does work if it was just this workbook.

    But the purpose in finality is that this workbook will be a lookup table to many hundreds of other workbooks, and the INDIRECT function does not work when linked to a closed workbook.

    Need a single table.

    *Could* use separate range names.  A lot of work, but that would do it.

    Wonder if there's vba that would create -- then maintain -- dynamic ranges from a list of cities.   ???

    Thanks again.

     - Mik

    Friday, March 20, 2015 4:56 PM
  • This is a bit off-topic but what the heck...I'm an off-topic kind of guy.

    So, my question is why are you trying to do a pricing/shipping function in Excel? There are shipping/order management/billing apps that do this kind of thing because they're, well, shipping/order management/billing apps. If the idea is to build something using a tool you have available, then somewhere you pay the cost for choosing to use what amounts to the wrong tool.

    I know this may be out of your hands so I appreciate the corner you may find yourself in but if you have anything to do with tool selection, back up and look at the problem space and maybe look at alternatives.

    Best of luck!

    Friday, March 20, 2015 5:08 PM
  • It is not pricing/shipping, not anywhere even remotely close to it.

    The example is merely analogous.

    The "Fees" need to feed hundreds of other excel sheets in a theater ticketing system, simply as a lookup table.

    Albeit quite large.

    Thanks,

     - Mik

    Friday, March 20, 2015 5:27 PM

  • a) Isolates the criteria city within the table.

    b) Then performs a lookup on the Fee [between the "bookends"] and returns the correct service charge.

    Put your LOOKUP TABLE from the first post into a new sheet, start at cell A1.

    Put your LOOKUP RESULTS into the same sheet, start  at cell E1.

    Use this formulas into this cells:

    H2:  {=MATCH(1,(A:A=E2)*(B:B>=F2),0)-1}
    I2:  {=MAX(IF(A:A=E2,C:C))}
    J2:  =IF(ISERROR(H2),I2,INDEX(C:C,H2))

    Drag the formulas down, column J shows the expected results for "Service Charge".

    Note: The formulas are inefficient, you can optimize them by changing A:A, B:B C:C to the size of your data, e.g.: A1:A1000, B1:B1000, C1:C1000

    Andreas.

    Friday, March 20, 2015 6:46 PM
  • Andrea,

    Thank you, that must have been a lot of work [I've been at it for weeks ..!].

    The example is analogous and very small comparitively.  These three cities are not tied together, there are indeed at least three hundred cities, each stand-alone.  Was merely using these as an example that different Fees attract different service charges. [Please have a look at my recent replies.]

    In real-world, numerous sheets with the "results" formula [the objective here] will have a drop-down for City, with multiple Fees generating their respective Service Charges.

    So in lookup results Denver would have all five Fees generating all five different Service Charges.  And so on ..

    Also, although it looked like you're on the right track with the first formula, when using that array the service charges returned from it do not equal the intended.  See table below.

    If there's anything that can be achieved modifying the first of your array formulas, that would be the key to this quite significant problem.

    Thanks again ...!!

    City          Fee         Service Charge Intended     S/C Returned w/Array
    Denver $49.99 $0.50                                                 2.00
    Denver $50.00 $1.00                                                 2.00
    Denver $99.99 $1.00                                                 3.00
    Denver $100.00 $2.00                                                 3.00
    Denver $149.99 $2.00                                                 4.00
    Denver $150.00 $3.00                                                 4.00
    Denver $199.99 $3.00                                                 5.00
    Denver $200.00 $4.00                                                 5.00
    Denver $5,000.00 $4.00 #N/A

    Friday, March 20, 2015 8:29 PM
  • Andreas, one more note.

    Per the table just sent, the Fees are not fixed.

    They fluctuate *by the penny* in between the bookends, as you've likely gathered from that table.

    The solution is *not* a true/exact match lookup, it's a false/approximate match lookup.

    That's why the LOOKUP(...  function is ideal, but it can't isolate both the City*and* it's respective Fee.

    Thanks,

     - Mik

    Friday, March 20, 2015 8:38 PM
  • They fluctuate *by the penny* in between the bookends, as you've likely gathered from that table.

    The solution is *not* a true/exact match lookup, it's a false/approximate match lookup.

    I suggest that you upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    A solution for this problem is definitely possible (maybe with a macro),but without the exact data, our current approach only guesswork... it could be years before we found the solution.

    Andreas.

    Saturday, March 21, 2015 7:45 AM
  • Hi Andreas,

    Here's a file that works within itself, but does not work for what is required.

    Using INDIRECT along with each city's hypothetical price/fee structure produces correct results, as it isolates the cities with their specific prices, so the lookup will work properly.     ... Which is specifically the problem to be solved.

    What is required is that the same results occur derived from *one large table*, exemplified on the Summary sheet, so other files can lookup to it concurrently.

    Also, there are not enough sheets available in a workbook for the number of cities [three hundred or more].

    File link below ... change prices in green shading to anything [some dramatic examples in there now], and yellow-shaded formulated cells will calculate correctly.

    https://www.dropbox.com/s/3oy12snsd6bmu4u/FeesSCs-Dropbox.xlsm?dl=0

    Thanks sincerely for your assistance.

     - Mik

    Saturday, March 21, 2015 12:15 PM
  • Andreas,

    One major aspect I forgot to mention in the preceding msg, although it may be blatantly obvious anyway ...

    Ideally, lookup results would be derived from the "information" table below on the Summary sheet.

    Thanks,

     - Mik

    Saturday, March 21, 2015 12:24 PM

  • What is required is that the same results occur derived from *one large table*, exemplified on the Summary sheet

    A formula for this becomes complicated, but we can do it with a very simple UDF, do as follows:

    Open your sample file
    Open the VBA editor
    Add a regular module
    Paste in this code:

    Option Explicit
    
    Function FindFee(ByVal City, ByVal Price, ByVal Where As Range) As Variant
      'Where must be a 3 column range in this format:
      '  1st column City, 2nd column Price, 3th column Fee
      Dim FirstCity As Range, LastCity As Range
      Dim FirstCell As Range, LastCell As Range
    
      Set FirstCell = Where.Columns(1).Cells(1)
      Set LastCell = Where.Columns(1).Cells(Where.Columns(1).Cells.Count)
      Set FirstCity = Where.Columns(1).Find(City, LastCell, xlValues, xlWhole, _
        SearchDirection:=xlNext)
      Set LastCity = Where.Columns(1).Find(City, FirstCell, xlValues, xlWhole, _
        SearchDirection:=xlPrevious)
      FindFee = WorksheetFunction.VLookup(Price, _
        Range(FirstCity, LastCity).Offset(, 1).Resize(, 2), 2, True)
    End Function
    

    Close the VBA editor
    In cell B6 use this formula:
      =FindFee(B$4,B$5,$B$17:$D$31)
    Drag the formula to the right till D6

    The formulas in row 9 and 12 are equivalent.

    Andreas.

    Saturday, March 21, 2015 6:46 PM
  • Thank you Andreas,

    Thank you ...!

    Looking at how to implement some workarounds, ie putting the custom function into numerous workbooks and them look up to a master table, etc.

    But this will do it.

    Presume the lookup table itself can be placed anywhere on any sheet, as long as it has "City" in the first field so the 'find...' portion of code will work.   ????

     - Mik

    Saturday, March 21, 2015 8:04 PM
  • Looking at how to implement some workarounds, ie putting the custom function into numerous workbooks and them look up to a master table, etc.

    The alternative is to place the UDF in an AddIn...

    You can also use this formula in cell B6 instead, it's similar to my code:
    =VLOOKUP(B5,OFFSET($C$17,MATCH(B4,$B$17:$B$31,0)-1,0,COUNTIF($B$17:$B$31,B4),2),2,TRUE)

    The lookup table can be placed anywhere, that doesn't matter. Also the input fields "City" and "Price" can be placed anywhere, in this points there is no difference between an UDF and a formula.

    The only precondition is that the cities must be in consecutive cells and the associated price must be in ascending order.

    Andreas.

    • Proposed as answer by Dogubob Tuesday, March 24, 2015 4:01 PM
    • Marked as answer by Mikf Saturday, March 28, 2015 5:24 PM
    Sunday, March 22, 2015 8:15 AM
  • Andreas,

    That's the key to isolating the City and Price that I was missing.  Backwards, sort of.

    Instead of starting with City, start with Price, match it to City and then isolate City using a CountIf in Offset's Height argument.

    Brilliant.

    THANK YOU ...!

      - Mike

    Sunday, March 22, 2015 12:35 PM
  • Andreas,

    Wow, very cool. I am trying to wrap my head around what this formula does and I'm hitting a bit of a wall.  Could you explain where my understanding is breaking down?

    I'm taking the sheet from dropbox and switched cell B6 to the formula you provided. Works great.

    I then tried to break down the bits of the formula to understand what's happening.

    The 'Match' bit returns the first row where we find the city name.

    CountIf returns the number of occurrences of that city name.

    If I copy:

    =OFFSET ($C$17, MATCH (B4, $B$17:$B$35, 0) -1 ,0, COUNTIF( $B$17:$B$35,B4), 2)

    into a cell, I get the '#VALUE!' error. The problem seems to be connected to the trailing ',2) at the end of the formula but I'm not sure why.

    The vLookup has me totally confused. How does the result of the Offset turn into a table array? (maybe once I understand why my offset is returning an error and see the actual output, it'll make sense???)

    Thanks for your feedback. Again, very nifty trick! Thank you for posting.

    Monday, March 23, 2015 7:42 PM
  • =OFFSET ($C$17, MATCH (B4, $B$17:$B$35, 0) -1 ,0, COUNTIF( $B$17:$B$35,B4), 2)

    into a cell, I get the '#VALUE!' error. The problem seems to be connected to the trailing ',2) at the end of the formula but I'm not sure why.

    No, the ",2" is correct. The issue is that OFFSET returns an array and so you have to enter the formula as array formula. Simple example:

    Copy this values in a sheet start at A1:

    1 2 3
    4 5 6
    7 8 9

    Now we want to have the 4 numbers in the "bottom-right edge" (5,6,8,9) as they appear in the cells E1:F2

    The formula for that is
    =OFFSET(A1,1,1,2,2)

    When you enter the formula into E1, you'll get the #VALUE error. When you enter the formula press CTRL-SHIFT-ENTER instead of a single ENTER and the formula appears with {} around:

    {=OFFSET(A1,1,1,2,2)}

    and the value in E1 is now 5. Now select the cells E1:F2, press F2, then CTRL-SHIFT-ENTER and you see all values.

    That is the basic idea behind my formula: I copy only the related part of the table "into" VLOOKUP.

    Andreas.

    Tuesday, March 24, 2015 8:16 AM
  • Andreas,

    Thank you, I've worked with Excel for a long time and never had to use the ctrl-shift-enter thing. I need to dig into that a bit to make sure I understand what it means (and I'm OK doing that research).

    I'm still confused bout how the 'Offset' section of the VLookup gets turned into a table array/range. When I compute just the offset output (using the ctrl-shift-enter magic keys), I get the first 'price' for the selected city. If I rewrite the vlookup using this value, I get something that looks like =vlookup(b5,1,2,true) if the city is Denver and the lowest price in the table is $1. If I enter the formula or c-s-e the formula, I get #N/A.

    Which bit of Excel magic am I missing?

    From Excel Help Vlookup(lookup_Value, table_array, col_index_num, Range_lookup)

    When I look at your formula, I interpret it as:

    • Lookup_value = B5
    • table_array = The entire offset clause including the match and countif formulas
    • col_index_num = 2
    • range_lookup = True

    Tuesday, March 24, 2015 11:47 AM
  • When I compute just the offset output (using the ctrl-shift-enter magic keys), I get the first 'price' for the selected city.

    Which bit of Excel magic am I missing?

    That is correct when you use only one cell for the array formula. When you use more cells, you get more values. OFFSET returns an array always!

    Download the sample file and my formula in B6 as discussed above. Then select cell B6, go into the Formulas tab of the ribbon, in the Formula Auditing group click the Evaluate Formula button.

    Click the Step In and Evaluate button and before the last calculation you can see this (except the language is English):

    As you see the OFFSET part has changed to $C$17:$D$21, because the MATCH part returns 1, 1-1 is 0 means OFFSET(C17,0,0,... is C17 and the COUNTIF part returns 5, means OFFSET(C17,0,0,5,2) becomes C17:D21

    I'm sorry, I do not know how I can explain it better. The best way is to use the Evaluate Formula window and debug the formula step by step, then you can see whats going on.

    Andreas.

    Tuesday, March 24, 2015 2:07 PM
  • Andreas,

    You explained it perfectly, I just didn't understand the implication of ctrl-shift-enter. I found a nice long article on the topic elsewhere and now get that c-s-e results in an array rather than a single value. I still haven't fully appreciated the implications, but I do have a better sense of how the vlookup works.

    My sincere thanks for your patience and willingness to take me through this process. Every time I touch Excel, I gain an appreciation of how little of it I understand.

    Doug

    Tuesday, March 24, 2015 2:38 PM