+ Reply to Thread
Results 1 to 8 of 8

Need to run VLookUp with IF Function to get proper rate aout of all different rates

  1. #1
    Registered User
    Join Date
    10-08-2019
    Location
    Calgary, Canada
    MS-Off Ver
    MS 365
    Posts
    13

    Need to run VLookUp with IF Function to get proper rate aout of all different rates

    I like to get rate value for city based on different rate brackets from another sheet. Example : Rate for Sudbury from Brampton if the weight is between 500-1000 lbs and rate if the weight is between 1000-2000 and so on ,I have file ready with me. In cell Q2 (CTT Rate) I need rate for Sudbury (J2) from rate sheet "CTT 6 LBS Rate, for the order weighing 517.00 lbs. Currently under CTT 6 LBS Rates file has 8 different rates as per the weight brackets. As the order has the actual weight of 517 lbs, I want system to do V lookup and choose rate from J column (500-1000) for Sudbury as 28.42 I am able to run the scenario with simple VLOOKUP but currently I have to bifurcate the file in 8 different sheets as per its weight bracket. I need your help to get all the calculation done on 1 sheet only. Your help is really appreciated.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by raviraval86; 10-08-2019 at 05:16 PM. Reason: FOrgot to attach attachment

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need to run VLookUp with IF Function to get proper rate aout of all different rates

    Change the headings in cell I2:P2 of the Rates sheet so they only show the bottom value of the range, i.e. 0 in I2, 500 in J2, 1000 in K2, and so on. Then you can use this formula in cell Q2 of the Orders sheet:

    =VLOOKUP(G2,'CTT 6 LBS Rates'!$F$3:$P$159,MATCH(J2,'CTT 6 LBS Rates'!$I$2:$P$2)+3,0)

    Copy this down as required.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Need to run VLookUp with IF Function to get proper rate aout of all different rates

    Don't know did these formulas match your request.

    I've use many helper columns for slove the request.
    Step of calculation. ( No change made to 'CTT 6 LBS Rates' )

    1. Declare size of rates in 'CTT 6 LBS Rates'
    Y1
    Please Login or Register  to view this content.
    ** I really want to refer for all rows in 'CTT 6 LBS Rates' and/or want to use INDIRECT
    but @Glenn was advise (about INDIRECT side effects) and show me how to refer ranges without using INDIRECT function
    by using :INDEX() in next steps. **

    2. Find the row in 'CTT 6 LBS Rates' that match [ Sender's City and Destination City ]
    Z2
    Please Login or Register  to view this content.
    (copy down to end of data area)

    3. Set lower limit for each rate in AB1:AI1
    and list all rates (refered form row in #2)
    AA2
    Please Login or Register  to view this content.
    (copy down to end of data area and to column AI)

    4. Compare between minimum rates VS rates summary for each weight range.
    Q2
    Please Login or Register  to view this content.
    Regards.
    Attached Files Attached Files
    Last edited by menem; 10-20-2019 at 05:47 AM. Reason: Add code tags

  4. #4
    Registered User
    Join Date
    10-08-2019
    Location
    Calgary, Canada
    MS-Off Ver
    MS 365
    Posts
    13

    Re: Need to run VLookUp with IF Function to get proper rate aout of all different rates

    Thanks a lot pete it worked,. Its great help.

    Can you please help me two of the following things on same formula just so that i dont simply copy paste it and do understand the part of it as well.

    =(VLOOKUP(G2,'CTT 6 LBS Rates'!$H$3:$N$159,MATCH(J2,'CTT 6 LBS Rates'!$K$2:$R$2,1)+3,FALSE)

    in this formula
    J2 actually stands for which cell? I mean it refer to which cell?
    how to logically calculate +3 in formula? I mean why did you put +3 in formula? based on which relation?

    If I can understand these two then I dont have to simply copy paste the formula but it will also help me to use the same kind of formula in future for other work as well.
    Last edited by raviraval86; 10-09-2019 at 12:31 PM. Reason: Need to ask further details

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need to run VLookUp with IF Function to get proper rate aout of all different rates

    Glad to hear it, and thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need to run VLookUp with IF Function to get proper rate aout of all different rates

    It's not a good idea to edit a post after you have received replies to that post, as we do not get notification of the edit.

    J2 refers to your Billed_weight on that row. This is compared in the MATCH expression to the values in the range I2:P2 on the Rates sheet (well, in my formula it does) to find which of those columns we should get the values from, and the MATCH function will return a number from 1 to 8 in this instance, i.e. it is the column number relative to that range. However, the VLOOKUP part of the formula is using a range of F3:P159, as your lookup value is in column F of the Rates sheet, hence we have to add 3 onto the value returned by the MATCH function to get the relative column number for this later range.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    10-08-2019
    Location
    Calgary, Canada
    MS-Off Ver
    MS 365
    Posts
    13

    Re: Need to run VLookUp with IF Function to get proper rate aout of all different rates

    Thanks a lot Pete for not only helping me with the problem but to provide with explanation to steps as well. Now I am able to understand the formula as well. It really helped me a lot to finish my one of the other project. (As I just ran the same one and it worked)

    Apologies for editing the reply and resubmitting . I am pretty new to forums as well in general and new tothis site as well so ended up mixing things. will be careful next time so that anyone like you who is helping does not have to face any difficulties.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need to run VLookUp with IF Function to get proper rate aout of all different rates

    Lots of people do it, but as I say there is no notification generated if you edit a post, and so contributors may not be aware of it. I only spotted it as I came back to check on this thread - otherwise I would not have known that you were hoping for a further reply (which may have seemed rude to you if I just ignored it).

    Just add a new post in future - it doesn't cost anything !!

    Pete

+ 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. Replies: 3
    Last Post: 06-13-2019, 05:07 PM
  2. 4 rates, if week reaches over 50 then Flat rate value ,Range date
    By dude6571 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-16-2018, 09:12 PM
  3. [SOLVED] Finding hourly rate with VLOOKUP function
    By vic.moon1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-11-2016, 10:05 AM
  4. Replies: 2
    Last Post: 02-09-2016, 10:40 PM
  5. [SOLVED] Formula to Validate Invoiced Rates Against a Rate Card
    By The_Snook in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-30-2014, 02:49 PM
  6. Replies: 0
    Last Post: 05-28-2013, 05:53 AM
  7. Calculating Month Average Exch Rate from Exchange Rates Calendar!!!
    By StanUkr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-14-2005, 06:12 AM

Tags for this Thread

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