Advanced Excel

Lookup Function on Excel

For some people the only mention of the word Excel makes them scared, this is comprehensible because Excel is a really powerful tool that has so many uses, formulas, and tricks that it would take a while to mention them all, let alone learn them all.

Sometimes, Excel seems like a dream come true. All you need to do is enter a formula, and your days of doing everything manually are pretty much over. Excel is your ally when talking about doing your job easier, faster, and more efficiently.

One of the functions that I find really useful is called the Lookup function. This function helps you find the Last Match in Excel, instead of the first match as the Vlookup function does.

However, before diving into the Lookup function let me give you a brief description of the Vlookup function and how to use it. VLOOKUP is used when you need to find things in a table or a range by row. For example, look up the price of a fruit by the name, or find an employee’s name with their employee ID.

The VLOOKUP function structure is as follows:

=VLOOKUP(What you want to look up, where you want to look for it (range), the column number in the range containing the value to return, return an Approximate or Exact match (indicated as 1=TRUE, or 0=FALSE).

For example, look at the table below with the employees of a store, their Employee ID, and their salary:

Warning: Undefined array key "title" in /home/amphyblogdev/domains/amphy-blog-dev.adcore.com/public_html/wp-content/themes/sentrysite-corporate/inc/courses-wi.php on line 29

Learn Computer Programs Online

Learn Basic Python Programming in 10 Days

Java Object-Oriented Programming

Learn Google Ads For Beginners (5 Sessions)

Explore More >

Sometimes, Excel seems like a dream came true. All you need to do is enter a formula, and your days of doing everything manually are pretty much over.

If you need to search for a specific employee’s salary you can use VLOOKUP and the formula returns the first match found by Excel, in this case, if you look for the employee ID 101 Excel will return $10,000.

If you use the LOOKUP function, Excel will return the last match among the repetition of the same item. For example, in the table, Employee Peter Smith appears 2 times, if you want to return the last match for this employee you need to use the below function:

=LOOKUP(2,1/(range of cells where to look for the item=cell address where the lookup value is stored),range of cells from where the match result is returned)

For this example when using =LOOKUP(2,1/($A$2:$A$5=$F$2),$C$2:$C$5) Excel returns the last match for this employee which is $11,000.

You can use any of these functions according to what you need to succeed at work!

Another function that will help you a lot to complete your work while using Excel is Conditional Formatting.

Conditional Formatting is a useful Excel function that highlights or changes the color or the appearance of cells given a certain condition. For example, you might want to change the color of the font of any number below a certain value to red, or change the background color of any cell that contains a certain word or value. Conditional formatting allows you to make a colorful sense of data in a messy or noisy world.

Suppose you have a performance by suppliers every month. Just three clicks can reveal the suppliers whose performance was below or above 99%.

To create a new rule just select the cells where the values are stored, go to the “Home” tab and click on “Conditional Formatting”, choose the Rule type you want to use, the reference value, and the format your cell will have is the reference value is true, see below:

After creating the new rule, the table will look as below:

Another really powerful, and intimidating, a function that can be used in Excel is the pivot tables. It is powerful because it can help you summarize and make sense of large data sets. Intimidating because pivot tables have always had a reputation for being complicated.

The good news is that learning how to create a pivot table in Excel is much easier than you may think! Before we dig in, let’s make sure we understand exactly what a pivot table is, and why we might need to use one.

A pivot table is a summary of data, shown in a chart that allows you to report on and explore trends based on the information. Pivot tables are useful if you have a large quantity of values you need to track, compare to one another, and group data in different ways so you can make conclusions more easily. The “pivot” part of a pivot table comes from the fact that you can rotate, or pivot, the data in the table to view it from a different perspective. You are not adding to, subtracting from, or changing the data.

Now that we have a better sense of what pivot tables can be used for, let’s start to actually create one!

Step 1, Enter the data into a range of rows and columns: Every pivot table in Excel starts with a basic Excel table. To create this table, simply enter the values into a set of rows and columns. Use the topmost row or the topmost column to categorize the values by what they represent.

For example, to create an Excel table of fruits and vegetables sold to different countries, you might have a column listing each “Order ID”, a column listing each “Products”, a column listing each “Category” and so on, see the table below:

Step 2, highlight the cells to create your pivot table: Once you’ve entered data into your Excel worksheet, highlight the cells you’d like to summarize in a pivot table. Click Insert along the top navigation, and select the PivotTable icon.

Step 3, drag and drop fields into the Row Labels area: Excel just created a blank pivot table for you. Next, you need to drag and drop a field, labeled according to the names of the columns in your spreadsheet, into the Row Labels area. This will determine what unique identifier the pivot table will organize your data by. For example, let’s say you want to organize the date by product. To do that, you simply need to click and drag the “Product” field and make sure it is visible in the “Row Labels” area. Please note that your pivot table may look different depending on the version of Excel you are working with. Nonetheless, the general principles remain the same.

Step 4, drag and drop a field into the “Values” area: now that you have established what you need your data to be organized by, the next step is to add in some values by dragging a field into the Values area. Let’s use the products data example, if you want to summarize the products by quantity, drag the “Products” field into the Values area, the pivot table is as below:

Step 5, fine-tune the calculations: The sum of the value you have chosen will be calculated by default, but you can easily change this to something different, such as average, maximum, or minimum depending on what you need to calculate.

If you are using a PC, you just need to click on the small arrowhead next to the value and select Value Field Settings to access the menu, see below:

We just covered the basics of pivot table creation in Excel. With this understanding, you can figure out what you need from the pivot table and the sky’s the limit.

Should you want more information or want to learn these functions in more detail please check the below references: