First field is port name & other is weight slab like below
Chandoo.org/wp/ - my blog is a passionate community where I learn and share excel tips, charting tutorials, visualization ideas, downloadable excel tutorials. It has 500+ excellent tutorials, examples and downloadable workbooks for you. Excel and Charting Forums Excel Formulas - Easy to understand help. Chandoo.org - Learn Excel, Power BI & Charting Online. Fresh Excel Tips, Power BI, Power Pivot, Power Query, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity. 25 Excel Tips E-book from Chandoo.org Display decimals when number is less than 1 Here is a quick excel number formatting tip. If you ever want to format numbers in such a way that it shows decimal values only if the number is less than 1 you can use conditional custom cell formatting (do not confuse with conditional formatting). Learn more about VLOOKUP & other lookup functions. Comprehensive and easy to understand This is a book for everyone who uses Vlookup. Most of us think Oh. I already know the function. But this book will open your eyes to some brilliant techniques which can be used across Excel. Nitin Paranjape Solid introduction to. =VLOOKUP ( 'BCD', A2:D6, 3 ) www.chandoo.org Become Awesome In Excel. Title: CheaterJohnExcelVLOOKUPChandoo Subject: Kopyalanmasi Kesinlikle Yasaktir. Aksi durumda hukuki islem baslatilacaktir.
| Port Name | Place of Delivery | Weight Slab | 10000 | 23000 | 26000 & above | 10000 | 23000 | 26000 & above |
| - | - | Container Size | 20 | 20 | 20 | 40 | 40 | 40 |
| - | - | - | Rate | Rate | Rate | Rate | Rate | Rate |
| PIPAVAV | KATHUWAS | - | 43500 | 62000 | 63250 | |||
| PIPAVAV | Patli | - | 47500 | 56250 | 63250 | |||
| PIPAVAV | Garhi | - | 55500 | 68250 | 75750 | |||
| PIPAVAV | ACTL | - | 58900 | 73200 | 82850 | |||
| PIPAVAV | Pyala | - | 58900 | 73200 | 82850 | |||
| PIPAVAV | Dadri | - | 52670 | 64022 | 71962 |
I have to pick rate of port wise, place of delivery wise, weight wise & container size wise :
for exampleChandoo Excel Tutorial
Pipavav Khatuwas 20 feet container with 10027 KG.
I have provided extensive training for some employees at my office. During training to gear up for an outsourcing project, I trained over 100 people in a variety of systems and enterprise solutions such as SAP and other internal web based software to retrieve information. By far the most impressive tool I have shown to others is the VLOOKUP formula in Excel.
The usefulness of this formula is much greater than this, but here is a bit of a background on how I came to use VLOOKUP so often. One of my first assignments as a Accounts Receivable representative, I was asked to process a check with over thirty pages containing store numbers, invoice numbers and amount paying for each invoice. At the time, the person who trained me said I had to do it manually, because “it has always been done this way”, which, unfortunately, is very common response at many offices. I decided to challenge the status quo and scanned the image to PDF and selected the invoices numbers and amount, matching these against our open receivables. The job was done in a couple of hours on the first time, and the time spent declined further later.
I will try to demonstrate this in a simple way so it can be applied to other areas as well, and not only Accounts Receivable.
Step 1 – Select the data sets
For VLOOKUP to work there must be at least two data sets. In both sets you have a list of items with the information needed to complete a particular task. In the case of the Accounts Receivable example, you have (1) list of invoices being paid and (2) list of open invoices in A/R.
To summarize our tables, we have a list of open receivables with invoice number and amount. We also have a list of invoices the customer is paying. We could manually find each item using the find function in Excel, but just imagine doing this for thousands of items!
Step 2 – Preparing for the formula
Add a column to one of the data set. I typically prefer the one with less records; in this case, the check. The goal is to find which receivable items should be cleared with this check.
Step 3 – The formula
There are four fields to be entered on the VLOOKUP formula:
Lookup_value – this is the cell in the existing data set that might have a match on the second data set
Table_array – this is range of cells in the second data set that might contain a match to the first
Col_index_num – is the actual number of columns to the right that contains the record you are looking for. In this case we are looking for the amount; thus, look at column I (invoices) to return column K (amount): column number 3.
Range_lookup – this is either “false” or “true”. In finance, I have never used true because it may return similar records and we generally want 100% match.
You can also read the Excel explanations for each item when placing the cursors on their corresponding boxes. Note the circle highlighted formula result returns $315.00, which matches to what the customer is paying.
Chandoo.org Vlookup
Copy the formula down to populate the results for each record. We can also add another column to show any discrepancies between what we have opened and what the customer is paying. This function helps a lot when it is time to reconcile the two amounts.
Chandoo Excel Forum
As I have mentioned earlier, it is difficult to illustrate the usefulness of this function. Whether you need to use for a data set at a large company, or to simply find corresponding addresses for invitations, this Excel function can save you a lot of time. If you want to explore more about VLOOKUP, there are other great posts from one of my favorite site: Chandoo.org. You may also want to watch a video I selected, which is very similar to the excercise we practiced on. I hope you enjoyed and learned something new. Please keep following me for some great day-to-day Excel tips.