Hi,
I'm looking for some help on a workbook. The goal is to find the cost of sending a pallet or multiples of to a customer. There are several criteria's which determine cost.
These are the criteria:
The postcode of the customer falls into a zone (1 - 10)
There are different price bands for an economy or premium service
There are different prices for 3 types of pallet and you can mix them in the same consignment.
i can use a VLOOKUP to get customer details and correct zone but cant work out how to use different pricing bands for service or pallets. i think i need INDEX and MATCH but dont know how to use them properly.
any help would be gratefully received.
Many Thanks Darren
Hi Darren,
Welcome to our forum! Can you please upload a sample Excel file that illustrates the data layout and desired result so we can help you.
Also, let us know what version of Excel you have as it sounds like the FILTER function might work for this, but it's not available in all versions of Excel.
Mynda
Thank you. Apologies I thought I had uploaded the file, my Excel version is office 2019.
I will upload after the weekend.
once again many thanks for help.
Darren
Hi,
My Excel version is Home & Business 2016
My goal is to get a total price for consignment and then to record it and check it against the invoice on the match sheet.
The pallet network delivers pallets(s) next day (Premium) or 2 day (economy) service.
Price of the consignment consists of the following criteria:
Postal zone (zone 1 - 10b)
Service level (Premium or Economy)
Pallet size (Full, Half, Quarter). You can have a mix of all sizes in the same consignment for e.g. 1 full, 2 half's and 1 quarter.
Any added extra's like AM delivery or Home Delivery.
Hi Darren,
Thanks for sharing your file, however it's not clear to me where the formula is that you're having trouble with as the MATCH sheet appears complete to me.
Please let us know the sheet and column containing the fomulas you want help with, and the desired result along with the logic explained in English, so we can help you.
Mynda
Thanks Mynda,
I have looked into the feeding formulas more on "Match" sheet and realised that i was getting the wrong answer in Total Cost column because the formula in match full column was not picking up the correct row in the "Tariff22" sheet. i have now corrected this and added a IF formula to get the right level of service. The Total Cost column now gives me the correct cost for that consignment.
I would like to build a new customer quote table on a new sheet to find the correct cost for a consignment by using their Postal code made up from the first 2 letters and first 2 numbers for e.g. IP25. i would need to match it to the Correct Zone for that code in "Postal code" sheet. i don't know how to do this and would be grateful for any pointers.
Many thanks for the help
