Excel VLOOKUP Function

Introduction

Now, this is a long guide, which should not concern you, as the VLOOKUP in excel is a big but important subject. Now, if you are a kind of person who prefers video then you can get the concept and introduction from this video. After that, I strongly recommend you to 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… pornogratis

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. dig this for details.

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 a lengthy option and often fraught with the risk of human error. 0Thankfully, 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, which 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.

Databases can save this date. One of the best gambling database is Ice Casino. Find here more about casino, games and bonuses.

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_valueisshownin 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 cellD7, 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 asProduct 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_arrayin 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 rule, (there is some exclusion)

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

Excel VLOOKUP function 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 VLOOKUP in Excel, 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.

VLOOKUP  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^thargument 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

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 Fin 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 the Excel VLOOKUP function 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 bounds 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 VLOOKUP 

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.

This 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!

How to Use VLOOKUP in Excel 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

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.

VLOOKUP alternatives

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 vs VLOOKUP

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 MATCHon 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 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 in Excel 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.

IF Vlookup in Excel

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.

VLOOKUP + 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 in Excel 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.

VLOOKUP with MATCH

Similar to INDEXand MATCH, you can use MATCHto 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 in Excel 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.

There is a more in depth guide on this site to comparing two columns of data with VLOOKUP.

Using VLOOKUP in Excel approximate match option instead of 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 66on row 6is 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.

VLOOKUP 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:

1. 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()))

2. Since this is an array formula, press shift+ctrl+enterto 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.

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

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

1. 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()))

2. 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))),)}

1. Drag the autofill handle to the right to apply the formula until #NUM
2. 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.

We hope this article was useful for you and if so we suggest to visit our sponsor – gaming platform Slotogate. A renowned platform, offers a wide selection of top instant play casinos and a complete range of classic and modern table games. It also offers lots of bonuses and strong player security measures, ensuring a safe and enjoyable gaming experience.