# VLOOKUP: The Ultimate Guide

__Contents__

Why do we need to lookup things?

Flying Dino Wrestlers to Business: Lookups for everything!

How can we use Excel to find the information we need?

How to use the formula in Excel?

The importance of vertically storing your data

As a general rule (there are some exclusions)

Looking up when there are duplicates

Counting the number of times, a lookup_value appears

Let’s look some alternative approaches to looking up

Index and Match might be better than VLOOKUP

Working in combination with the IF Statement for lookup power

Combine with TODAY() for automatic daily updates

Combining with the MATCH function to deal with ambiguity

Comparing/Reconciling data between 2 lists

Using the approximate match option instead of an exact match

How to handle looking up when there are multiple values

Looking up when you have multiple criteria

**Why Excel’s VLOOKUP will change your life?**

__Introduction__

__Introduction__

Now, this is a long guide, which shouldn’t concern you as the VLOOKUP is a big but important subject. Now, if you are the sort of person who prefers video then you can get the concept and introduction from this video. After that, I strongly recommend you keep reading the guide as we get progressively more advanced on route to helping you master this Excel Function!

**Why do we need to Lookup things?**

When I was a kid, I loved finding things out. I wanted to be the smartass know-it-all!

I was obsessed with Dinosaurs, Aviation, and most importantly Professional Wrestling. I would spend countless hours reading books on those topics. I have a very logical brain and I would love listing these things out, so they were organized enough for me to later memorize them (Did I mention I was a geek?!).

Then Like a true Dino-geek (that’s what they call us!), I would list them out like this…

Dinosaur |
Origin |
Length |
Weight |
Diet |

Anklyosauras | Plains | 12m | 7 Tonnes | Herbivore |

Allosaurus | Woodlands | 7m | 2 Tonnes | Carnivore |

Brachiosaurus | Woodlands | 30m | 80 Tonnes | Herbivore |

Gigatosaurus | Swamps | 12m | 8 Tonnes | Carnivore |

… | … | … | … | … |

That way, I had a table of information I could use to easily find information associated with each dinosaur.

Discovering associated Information is at the heart of finding or looking up.

**Flying Dino Wrestlers to Business: Lookups for everything!**

In everyday life we need to find information, it could be searching for a phone number by looking up a name in your contacts list on your address book on your mobile device, or it could be the price of a menu item in a restaurant or online. In summary, both these processes involve looking at a name, or reference and then finding its associated value, that could be a telephone number, cost or anything else associated with that record.

This same principle applies to the business environment. Let’s say you are a Management Accountant working with Human Resource data to build the annual budget.

Now, you have a list of employees and their associated information such as salary for different resource types (i.e. Analysts, Project Managers, Developers, Infrastructure Designers, etc).

You know that you need to plan for 10 Analysts, 3 Project Managers, etc, so you need to start finding important data about these resources such as salary to start shaping a budget.

Perhaps your work involves the selling of products or services, these would generally have a product ID.

Let’s say a potential customer needs more information about these products, you can find that information with the product name or a reference number and once you know the reference number you have access to anything from the product name to its weight to materials.

**How can we use ****it to find the information we need?**

Often at work, you are in such situations where you need to find out some information from a record. Perhaps, as above you need to find the selling prices (for example) of a selected number of products by using the product IDs and looking through a huge master list numbering thousands of rows of data. Here you are faced with a choice, looking each product ID up manually and checking the price, then entering it into your report or utilizing it. This is the lengthy option and often fraught with the risk of human error. Thankfully Excel is a great tool to make jobs like this easier. If you’ve never heard of this function, then you are about to be initiated into the foundation of any Excel user’s skill set. I have found over the years that is the number 1 formula that Excel has to offer (joint #1 with IF).

It’s one of the most useful functions (my favorite formula along with IF!) and the function will make finding information effortless, efficient, and more accurate.

**What is a (concept)?**

In summary, it can use a given reference and look for it in a specified range and then return a value from the same row in another column. You can instruct the function to find an exact match or the closest match. The former is best used for IDs when you need to find a person’s employee number or a product ID, for example, the latter is useful for finding a graded result if the lookup criteria sit in between a bracketed range, more on this later.

The VLOOKUP function broken down looks like the example below, in summary, we will ask:

*Find my reference in a range of **VLOOKUP**, when you find it, please tell me the piece of information that is x number of columns to the right of that data*

**How to use the formula in Excel?**

Before I look at how to use this valuable VLOOKUP function in Excel, let’s first look at it in the form as found in Excel.

**=**VLOOKUP**(lookup_value, table_array, col_index_number, [range index number])**

Breaking down each element, Excel defines these as:

**lookup_value**

Is the value to be found in the first column of the table, and can be a value, a reference, or a text string. This is your employee or product ID I mentioned above.

**table_array**

Is a table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.

**col_index_number**

Is the column number in a table_array from which the matching value should be returned. The first column values in the table in column 1.

**[range index number]**

Is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE. Additional note, you can also use a zero **0(for FALSE)** or a one **1 (for TRUE)**.

**Basic Example**

Using the **Product ID** example above let’s look at how we can easily extract the price from a **Master List** by using the **Product ID** as the lookup reference.

Our **table_array** is highlighted in red. Our **lookup_value **must appear in the first column of the **table_array**, we can see it does in cell **B7.**In the example above our **lookup_value**isshownin cell** G3 **in blue, we’ve used a cell reference to indicate this, but you could enter **“D40” **in quotation marks as it’s text.

Next, we’ve told the function to look at **column 3** of the **table_array, **in this case, that is column** D, **note this is **NOT** column 3 of the sheet i.e. column C, it’s the third column within the range you have selected.

Finally, we’ve used the **FALSE or 0 **indicators to let the function know we want an **EXACT** match, 99% of the time you want exact matches, but I will discuss the 1% later, it is very useful when needed.

All of that combined means the function looks for the text string **“D40” **in the range highlighted in red above, finds it, then looks into column 3 of that range and returns the value shown on the same row. In this case, it’s**£2.25** as shown in the result of cell **G5**. We can manually trace across from **Product ID“D40”** and count across 3 (including the starting cell), to find we stop in cell**D7,** there we can see our result.

**The importance of vertically storing your data**

For this function it’s important to follow many practices, one of those is, as the title suggests, to store your data vertically. We can use the same example above to highlight this, one key thing to note is the V in VLOOKUP, as it stands for VERTICAL, so VERTICAL LOOK UP, that is literally what the function does, it LOOKS UP the criteria you want VERTICALLY.

As you can see below we have the Master List, this could be a sheet name. It’s generally good practice to ensure your raw data is formatted in this tidy manner, with one column for each set of data. This will make using functions of any type easier, it’ll also make your workbooks look clear and professional.

This below is better than…

The data is split over more columns than is needed, plus some of the data is in the wrong columns and column heading missing. A tidy, well-labeled worksheet is a friend to you and those others that may need to use it. A spreadsheet should be able to tell a story and give a clear indication of what the data is.

.. and also, this! it is NOT suited to data stored in this HORIZONTAL format. If we were to try to use VLOOKUP on the below it would expect to find all the criteria for a category in the same column, there are, however, none, as these are all stored horizontally.

**Locking the range**

If you don’t know already, putting a **$** sign before a column or row reference will **LOCK** that reference into the formula. What that means is, that no matter where you copy the formula down or across then that reference will stay as it is, it will not move relative to where you copy the formula to. If you do not use a **$** sign the column reference will move relative to where you copy the formula. Let’s look at that in action below.

We can use the same example again, but this time in column **G** we need to find the **Price £** for each of the 4 **Product IDs** shown in column **F. **Once your formula is entered into a cell you can drag the fill handle (little square in the bottom right corner) down as far as you need to. Happy days, right?

Not so, now we’ve dragged it down we can cross-reference the results and see they all match, but something seems to be not working with the last one circled in red.

Clicking on each cell will give a clue, the first cell looks ok, it found **Product ID “C30”**, and returned the result of **£4.50, ** that one works.

The second one has the correct data as**Product ID“D40”** appears inside the red range still. Note that the red range has come down one row at the top **AND** the bottom.

This pattern will follow through to the next cell until we reach the one with our error. It’s here the issue becomes apparent, our **lookup_value** no longer appears in our **table_array, **hence the **#N/A** result. If the function cannot find the **lookup_value** then it’s going to display an error.

To rectify this, we need to use the **$** signs around the cell references for the **table_array** like so. You can either add these in manually but it’s far easier to press the **F4 key** to toggle through the options when you select the range when first writing the VLOOKUP, or if you need to do it after then simply highlight the **table_array**in the formula bar and then press the F4 key.

**Before**

As per the **example** above, copying the formula down one row will change the **rows by 1** with each move, equally, copying the formula one column right will move the **column references down the alphabet by one** letter too.

**F4 key pressed once**

**$** signs before the **column AND the row reference**, this will retain the

**table_array**as

**B4: D10** no matter where you move it to.

**F4 key pressed twice**

**$** signs before the **row reference ONLY**, this will keep the row references the same in the

**table_array**no matter where you move it to.

**F4 key pressed three times**

**$** signs before the **column reference ONLY**, this will keep the row references the same in the

**table_array**no matter where you move it to.

A 4^{th} press returns it to the **Before stage
**

**As a general rule (there are some exclusions)**

If you copy a formula down you need to lock in the row ($ before the number) references.

If you copy a formula across then you need to lock in the columns($ before the letter) references.

If you copy a formula across and down then you need to lock in both references.

**Looking up from another sheet**

VLOOKUP from another sheet is almost the same as VLOOKUP on the same sheet. The only difference is that the sheet name is now specified in the table array (as shown in the example below). Use the VLOOKUP function, as usual, no need to manually type the sheet name. Just click on the worksheet where your data is located, select your data range and finish off with the column index number and lookup range to get the result.

**Looking up from another workbook**

To VLOOKUP from one workbook to another, open both workbooks you want to work with. Take, for example, 2 files named Workbook Source and Workbook Result (as shown below). Use this function in Workbook Result and select the value. Now go to the other workbook which is the Workbook Source and select the table array. Notice that the table array now contains the Workbook Name, Worksheet Name, and data range. Again, finish off with the column index number and the range to get the result.

When it is not working

When it is not working

Here are some guidelines in troubleshooting when it is not returning your expected result:

**When the **VLOOKUP **returns the wrong value**

There are several reasons why it may not work the way it’s supposed to. The most common cause of the trouble is neglecting the **range_lookup**. Although by default VLOOKUP does an approximate match, if this last argument of the formula is omitted and your **lookup_value** is not found in the table array, then it assumes that your table is sorted numerically or alphabetically. When it performs the search, it finds the value that is closest to the value. This mistake is very tricky as the results might look completely normal but can be a total disaster particularly with large, unsorted data. Here is one good example.

Note that our lookup value is **“H50”**, by looking at the table array, Product ID H50 is not on the list, but it finds a result anyway. To fix this problem, simply supply the 4^{th} argument **range lookup** with **FALSE** or **0**. In the exact match mode, it will return the correct result if the value is found and #N/A if not.

**Checking for spaces in V****Lookup**

There will be, or already has been, a time when the formula looks perfectly fine, the ranges are good, your lookup_value appears in your **table_array,** you have the correct **col_index_number** too. In the example below, we can see this in action using the same example again.

As you can see from the results in **column H**, we have a bunch of failed results showing as **#N/A**. You may notice that some of the **Product IDs** are not so centered in the column, this won’t be so easy to spot in larger spreadsheets with hundreds of lines of data so never rely on manual methods, let Excel do the work for you.

If you aren’t familiar with the function **TRIM**, you are about to have a crash course on it. Thankfully it’s **VERY** simple. In Excel’s own words it defines this function as being able to **remove all spaces from a text string except for single spaces between words, **and it looks like this **=TRIM(text).**

To ensure complete confidence that we resolve this type of issue it’s best to apply this to **BOTH** sets of data where we suspect the issue is. In this case, this will be the **Product ID**, in column **B and F**in the example above, see below. The **TRIM** function has been added to two new columns inserted in, but you can do this anywhere in your workbook or on another sheet if you want to by copying the Product ID from both columns.

Once you have applied this function to your data, simply copy each yellow range and paste values over the top of your original data. Proceeding with the first yellow range we can see some of the results have changed.

Finishing up with the second yellow range, we can see that all the results have now populated, you can now delete your other yellow columns.

This issue CAN and WILL cause the same problems in pretty much most functions, especially ones that involve some sort of search criteria, so that’ll be the likes of **IF, AND, OR, HLOOKUP, COUNTIF, SUMIF**, this list goes on.

**When VLOOKUP doesn’t return the correct value (extra space, didn’t lock range)**

When VLOOKUP work they can be your best friend but there are so many ways where they cannot work, let’s look at some examples so you can broaden your understanding, not only of this but also Excel as some of the troubleshooting techniques apply to many other functions too.

**VLOOKUP out of range**

When building your **VLOOKUP**, you are required to tell the function which **col_index_number** you wish to look in. if you tell it to look in column 4 when there are only 3 columns in your **table_array** then it’s going to find that impossible to return a result. We can see this in action below:

In summary, for a **table_array** of **x** length, you can state a **col_index_number** of 1 all the way through to and including **x**, this can also mean **x = 1**

**Other Errors (#N/A)**

You may have worked through all the troubleshooting above and still, found that there is an **#N/A** result, it may be that this is actually what we are looking to achieve. If you were given a list of **Products IDs** to search for current prices but found that one product doesn’t appear to be on the **Master List. ** Legitimate errors will give us a signal that the price is missing from the **Master List **and should therefore be added or followed up to find out why it’s missing. Naturally, of course, it could be that the **Product ID** we have is wrong in the first place! So, you might need to refer back to your source of information to check the information is correct.

Above we can see that the additional **Product ID **of** “X90”** is nowhere to be found in the **table_array**, this is where it will return an **#N/A**. Finding legitimate **#N/As** leads us nicely into the next subject.

If an additional column gets inserted into the red range this will also break the formula and stop it from displaying the correct result. It won’t show you any errors so be mindful that you have selected the correct **col_index_number**. The number you enter for this is a fixed number, so it won’t know you’ve added (or removed) any additional columns. It’s just not that clever. There is however a way around this by using **INDEX and MATCH**, more on that later.

Above, column 3 in the **table_array** is now **BLANK** ready for the additional data, this could be **TEXT** or **NUMERIC** data such as the example below. Notice the results now look out of place with the **Price £** heading.

**Using IFERROR to handle errors**

When faced with the above situation (see an example with #N/A result for Product ID X90) it’s clear enough that one **Product ID** is missing, but only because we have done all the checks and manually checked ourselves. If you are confident the formula is doing what it should and it’s a legitimate **#N/A**, then we can an additional feature to the VLOOKUP to improve the spreadsheet into ‘talking to us’.

Below is one of a few useful functions to append to the beginning of the function. As you can see below it returns a value if an error is found, if no error is found it performs that calculation anyway.

This function is made up of two parts, the value element can be an entire formula, in this case, we will be using our value from above.

Which looks like this when we start to add **=IFERROR(** at the beginning, note the bold part (circled in red) of the formula help bar is where we are in this particular function, this applies across all other functions in Excel.

Adding a comma will then enter us into the next required field, it’s here we can do anything else, it could be another function, a VLOOKUP or adding TEXT to be displayed. If you are set on the idea of displaying some sort of message then it’s important to enclose the text in quotation marks, like below circled in red.

After copying down, we have a clear message on what is going on here, note though that applying the IFERROR function to any other scenario we talked about above will also output the same message. Excel considers any #N/Aor#REF! as an ERROR, it won’t tell you why there is an error, you need to figure that bit out yourself. It’s a smart function but not AI!

**Looking up when there are duplicates**

One thing it will do is find the first match of the criteria on a row it comes to, (reading from the top, then vertically downwards) and then returns the result in the specified column on THAT row that it finds FIRST ONLY. It will ignore any other duplicated results; this is best shown as per below.

Above there are 4 extra rows of data in our table_array, the range has been adjusted to include a search in all the data and Product IDs C30, D40, F60, and A10 have all been duplicated and their Price £ had been changed. For Product ID C30, it’s increased from £4.50 to £5.60.

There are a few options to check for duplicates. This will depend on your data, should there be duplicates in the first place, perhaps some records are old and need to be removed, perhaps you NEED to have duplicates but show ALL the results. Let’s look at some checks/alerts you can do in order to do this.

**Counting the number of times a lookup_value appears**

If you want to find which Product IDs are appearing more than once you can use the function COUNTIF, there is an article on this for a more detailed explanation, so follow through below for a quick crash course.

Above, the function uses =COUNTIF(range, criteria), where the range is where we want to count how many times the criteria appears in it. The criteria, in this case, will be the Product ID. As you can see, each result returns a count of 2. This will identify which references appear more than once.

**Making unique references in your lookup**

Perhaps the reference you have is correct for both, but the description gives a bit more of a clue and indicates these are in fact two separate products. See below on how to make unique references.

So, imagine you have a Product ID and also a variant of that in the form of a Type (shown in the orange range), this could indicate a variety of fruit or veg in our example above. In order to build a unique Product ID, we need to build ourselves a new one that joins these two pieces of information together. We can use the & and quotation marks to join them.

The example above can be structured in any way you see fit to make them unique, so long as you follow this through to your final search criteria then you can use any, some, all or the following methods found in the example below.

Ok, so we’ve created our unique Product ID and updated the green section with our new codes too (manually), all that is left is to adjust the table_array where we need to find the new and unique lookup_value, you can do this by dragging the range or deleting the selection in the formula bar and then reselecting.

Above we can now clearly see the results are as expected. You may wish to run another COUNTIF check as this stage to make sure there aren’t any more duplicates of the Unique Product ID.

**Let’s look at some alternative approaches to looking up**

HLOOKUP is the cousin to VLOOKUP. I’m not a big fan of it (I‘ll save that for another time!) but in brief summary, it works with the lookup_value being displayed horizontally and NOT vertically. There is another article on this that explains this in more detail. The same troubleshooting methods will apply for HLOOKUP versus what we discussed above. To show a side by side comparison of why it works for one and HLOOKUP for the other, you can see the same set of data side by side below.

HLOOKUP will need to have row numbers (circled in red), in order to be told where to find your result. For HLOOKUP, you need to have the search criteria appear in the first ROW as opposed to the first COLUMN.

As I mentioned, I’m not a big fan of the HLOOKUP, primarily because it means you must move away from the idea of vertically storing your data, which is the best practice.

### Index and Match might be better

There are instances where the INDEX and MATCH combination is a better choice than VLOOKUP, one is dependent on how your table_arrayis organized, another is processing power, and finally for future-proofing when new columns get added in.

Firstly, let’s look at a scenario when the lookup_value is NOT in the first column of the table_array. Below is an attempt to use the same method as the original first example in this article, however, the Price £ and the Product ID have been swapped around. The column containing our lookup_valueis no longer in the first column; this will cause the result to be an #N/A.

You have a couple of choices here, simply move the columns around which is what many people will do if they aren’t aware of **INDEX** and **MATCH**, but what if you aren’t able to move them for some reason. What then? This is where the **INDEX** and **MATCH** will save the day.

### In summary, **INDEX** looks like the below.

**INDEX(array,row_num,[column_num])**

**Must include the column where our result is.**

**Which row number within that array our result is, and where we’ll use the MATCH function.**

**In which column number of the array our result is.**

Note above, we’ve mentioned the use of **MATCH** in the **INDEX** function, so yes, these are **two separate functions,** but we are combining them to make an alternative to a **VLOOKUP**.

In summary **MATCH**on its own looks like this.

**MATCH(lookup_value, lookup_array, [match_type])**

**The criteria we are trying to find in order to get our result it’s associated with.**

**The column where the criteria can be found.**

**Choose a 0 for an exact match or 1 for an approximate match.**

If we then combine **INDEX and MATCH** they look like this.

**INDEX(array,MATCH(lookup_value, lookup_array, [match_type]),[column_num])**

The resulting effect is shown above. This method also protects against any additional columns being added in between the two sets of data; this can be a cause for breaking a **VLOOKUP** where there is an inserted column within its selected range.

**Combining with other formulas**

Like any formula, **VLOOKUP** can be combined with many other functions.

**VLOOKUP with column() to speed up analysis**

As you have seen in our example, the **col_index_number** is a fixed figure but what if we wanted this to increase each time we copied the formula to the right? See below for an example of this.

You can see the **COLUMN()** formula on row 12 and the result just below, basically it will give you the numeric version of the column, so A=1, B=2, C=3 and so on.

Integrating this into the **VLOOKUP** in our example above means that for the formula found in **C10**, the **COLUMN()** will return a value of 3, so this will return the value from the 3^{rd} column in the **table_array (in red). **This formula can now be copied across, note also we’ve used a **$** sign in front of the **A10 (circled in red)**so the** lookup_value** remains locked in place.

This method is particularly useful if you want to retrieve a quick summary of one product. You can now change the cell **A10** from **D100 to S200** for example, this will update the **ANALYSIS table** for you as the **lookup_value** has changed.

**Working in combination with the IF Statement for lookup power**

I could write an entire book on this combination alone as I have found these two formulas to be Excel’s two most important!

Perhaps you want to perform an action if your result meets a certain criterion. You can utilize the power of IF statements, again, there is a set of articles on these, but let’s look at an example using this same set of data.

In the example above, we have appended the **IF** statement to the start of the **VLOOKUP** and tested if the result is **less than 10 (circled in red)**, so if that is **TRUE **we want to show a **FAIL**, alternatively i.e. 10 or over, we want it to show **Target Achieved.**

You can use all manner of calculations, not just displaying text but making calculations too.

In the example above, we can calculate the sales amount ONLY when 10 or greater unit sales have been achieved. To better illustrate this, the formula is split over a few rows in the formula bar to make it clearer. If you don’t know how to do this, all you need to do is simply hit hold **Alt and hit Enter.** This is very useful for making large formulations clear. There is an extra column with **Unit Price £** to help calculate the total sales cost.

**Combine with TODAY() for automatic daily updates**

If you aren’t familiar with the function **TODAY(),** it basically does what it says, it will display **TODAY’s** date. This can be useful for several things, to add into invoice templates, and to use within a **VLOOKUP**. One use could be for advising you of a key task that needs doing, all neatly stored in a spreadsheet that you can easily view and manage.

In the example above we have a list of the date in **April 2018**, the **DAY** (using =**TEXT(value,”dddd”)**) and then a brief description of a reminder for what is the key task today, plus the time it’s due by and then location it’s expected to take place.

The VLOOKUP uses the function **TODAY()**and will re-calculate this every time the workbook is refreshed; this is what is known as a **volatile function. **It’s best to avoid using a large number of these as they can slow down workbooks. For this example, it’s fine and will cause zero issues.

As per previous VLOOKUPS, our lookup_value must appear in the first column for a result to populate and we must specify the correct **col_index_number** to return the **Task, Due and Location** results. With each passing day, the results will adjust accordingly.

**Combining with the MATCH function to deal with ambiguity**

Similar to **INDEX**and **MATCH**, you can use **MATCH**to dynamically find the **col_index_number** you need.

Above we can use another variation of the **COLUMN()** function we looked at earlier. Where **COLUMN()** will encounter problems when the columns on the data vs analysis do NOT align i.e. Jan-18 in orange appears in the same column as Jan-18 in green at present so all is well, but moving the **ANALYSIS** table will mean the **column()** will change too, thus changing the result.

**MATCH, in this case,** is better suited to this task when you need to move the table elsewhere. The blue highlighted box above holds the **MATCH** formula and the great thing about this is that it will return a relative position of a lookup_value in a range even if you specify a horizontal or vertical range.

In this case, we can see the** Jan-18 (in E9) **appear in the 4^{th} position in our range **$B$3:$H$3**.It’s very important to ensure that the range you select in the MATCH function has the same width or height as your **table_array** in the VLOOKUP function, not doing so will mean the alignment will be off and your results will be inaccurate, see below.

If you select JUST the month in **E3:H3, **the **MATCH** function will look for **Jan-18** in the range you specify, but this time it will find it’s the first cell in the range, i.e. 1, so it will return a result of **S200**. You can see how this has caused **Feb-18** to think that **Sales Quantity** totals **“Shower Gel”** and so on for the other cells, in **Mar-18**, we see **6**, which is, in fact, the figure for **Jan-18** in the **table_array**

**Compare/Reconcile data between 2 lists**

A very popular use of VLOOKUP is to quickly check if two sets of data appear on one sheet vs the other and vice versa.

Here we have two sheets, one in green and one in blue, we have a list of references on both. In **column B for Sheet 1,** we would like to find out if any of them appear on **Sheet 2**. We can do this with VLOOKUP, to check for each reference we specify. Anything that isn’t found will return a result of **#N/A**, it’s here we know that some are **NOT** found on that sheet.

The same principle applies to **Sheet 2** below.

**Using the approximate match option instead of an exact match**

Earlier I mentioned that you should mostly use the exact match or **0** or **FALSE** for range_lookup, so why would you need an approximate match. It’s very useful for calculating a result from a series of data with tiered data, such as commission rates or also for exam grades and the like. You can achieve the same with a **nested IF statement** but that can get ridiculously messy if you have several tiered scores. VLOOKUP with approximate match makes quick work of this.

So, firstly, one **VERY** important thing about using approximate match is that for the function to work effectively you **MUST** store your data (Grading system in this case) in **ASCENDING ORDER**. We mentioned this at the very beginning of the article. What approximate match will effectively do is to find the next largest value that is less than your lookup_value, so in our example it will find that **66**on **row 6**is greater than our lookup_value, therefore it will revert to the previous figure above on **row 5**, in this case, it’s **51**, and the Grade on that row = D.

**How to handle looking up when there are multiple values**

VLOOKUP is basically intended to return only the first matched instance of a lookup value. However, there are ways to return multiple values by combining other functions in an array formula. You can either display the values vertically or horizontally.

**Return multiple values vertically:**

- To return multiple values vertically, use this syntax:

=**INDEX**(return_range, **SMALL**(**IF**(lookup_value = lookup_range, **ROW**(lookup_range) – **MIN**(**ROW**(lookup_range))+1,””), **ROW**()))

- Since this is an array formula, press
**shift**+**ctrl**+**enter**to complete the formula.

**Explaining the parameters of the array formula:**

Index() – returns a value or reference of the cell at the intersection of the row and column you specify

Small() – determines the K^{th} smallest value in the data set

If() – evaluates whether the condition is met, returns a value if TRUE and another value if FALSE

Row() – returns the row number

Min() – returns the smallest number in a set of values

Here’s an example. What we want to do is extract and enumerate the multiple entries of **Apples **from the list.

The formula used in cell F2

{=INDEX($B$2:$B$15,SMALL(IF(F$1=$A$2:$A$15,ROW($A$2:$A$15)-MIN(ROW($A$2:$A$15))+1,””),ROW($A$1)))}

**Tip: Be sure to lock the ranges before you paste the formula to other cells.**

- Drag the autofill handle
__down__to apply the formula until*#NUM*

You may notice the ** #NUM **error. It indicates that the formula cannot find any more results from the lookup_range.

- Trap the errors by inserting
**IFERROR**at the beginning of the formula.

{=IFERROR(INDEX($B$2:$B$15,SMALL(IF(F$1=$A$2:$A$15,ROW($A$2:$A$15)-MIN(ROW($A$2:$A$15))+1,””),ROW($A$1))),)}

**Multiple values horizontally:**

- To return multiple values horizontally, use this syntax:

=**INDEX**(return_range, **SMALL**(**IF**(lookup_value = lookup_range, **ROW**(lookup_range) – **MIN**(**ROW**(lookup_range))+1,””), **COLUMN**()))

- Press
**shift**+**ctrl**+**enter**to complete the formula.

Using the same data, we will extract the multiple entries of Apples from the list. This time, horizontally.

The formula used in cell G1

{=IFERROR(INDEX($B$2:$B$15,SMALL(IF($F1=$A$2:$A$15,ROW($A$2:$A$15)-MIN(ROW($A$2:$A$15))+1,””),COLUMN(A1))),)}

- Drag the autofill handle
__to the right__to apply the formula until*#NUM* - Trap the errors by inserting
**IFERROR**at the beginning of the formula.

**Looking up when you have multiple criteria**

Sooner or later you may encounter the need to lookup value with multiple criteria. One good illustration is finding value with First and Last names as criteria. You may be skeptical and ask, “Can VLOOKUP do the job?” ABSOLUTELY! A little help from CHOOSE function does the trick.

**How does it work?**

Step 1: Concatenate criteria 1 and criteria 2 using “**&**”. This will be our lookup_value.

Step 2: Insert Choose function in our second argument. Choose will serve as a virtual table, concatenating criteria 1 and criteria 2 columns. This will be our **table_array**. The concatenated columns will be treated as one column, hence, the “**2**” in our **column_index** argument.

**Syntax:**

{=VLOOKUP($L$15&$L$16,CHOOSE({1,2},$F$12:$F$21&$G$12:$G$21,$H$12:$H$21),2,0)}

**Conclusion**

I hope you have found the article useful and already realized where you can use it in your daily work.

As I mentioned before, VLOOKUP is one of the most useful functions in Excel if you don’t know it, you need to and if you are reading this, you should be able to start harnessing some of its amazing potentials.

Or did you just skip to the end? There is a lot here, so don’t expect all of it to stick. With any formula, you never really understand it until you start practicing it AND using it in the real world in a live environment.

Your ability to understanding Excel functions, how to best to use them, and how to troubleshoot will only improve by __experimentation__ and continued use.