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

1. ## 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.

2. ## 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. ## 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.

4. ## 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.

5. ## 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.

Pete

6. ## 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. ## 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. ## 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

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