How to use VLOOKUP, HLOOKUP and INDEX MATCH in Excel
Suppose you have a data table and you want Excel to find a specific value and return that value in another row. This requires a lookup function. Excel has a variety of features you can use to achieve this, including a more flexible but slightly more complex combination of VLOOKUP () and HLOOKUP () and INDEX () and MATCH ().
The Excel lookup feature looks very simple, but it’s very easy to get the wrong answer if you don’t fully understand how it works.
Excel has an additional lookup feature called LOOKUP (), but this feature is included only for compatibility with older spreadsheet applications, so I’ll focus on VLOOKUP () and HLOOKUP (). In the names of these two functions, V and H refer to vertical and horizontal respectively, so after learning how to use VLOOKUP (), it should be easy as HLOOKUP () works the same. But I have data arranged in rows, not columns.
The VLOOKUP () function assumes that information elements in columns with different data are arranged in different tables.
Look at how VLOOKUP () works.
This simple example also illustrates one of the problems with lookup functions. Only return the first match. As we can see there is another match that VLOOKUP () ignored. If you need to return multiple results from a table, the lookup function may not work. You need to filter the table or use a pivot table.
Now let’s look at a more important issue with the lookup feature. Optional, using fourth argument. Perhaps you don’t know about the fourth claim or aren’t sure about the spelling of “Rota,” so I think you use a rough match rather than an exact match.
What’s changed in the formula is to omit the fourth argument. There are several entries that exactly match the values in the table, but this function now returns a completely different album title. Rolling Stones album which doesn’t actually include & # 39; Whole Lotta Love & # 39; by Led Zeppelin. Changing the column index to 1 showed that Excel matched ‘Hongky Tonk Women’ and ‘Whole Lotta Love’.
So something is wrong…
This is because the approximation does not mean finding values that are & # 39; closest to the query value anywhere in the table.
Find the largest value in the table that is less than or equal to the lookup value. In fact, it is much more specific than that. If there is no exact match, it finds that the first item in the table is larger than the lookup value and matches the cell immediately above.
For this reason, unless you specify an exact match using FALSE as the fourth argument in VLOOKUP (), the data table should be sorted in ascending order using the leftmost column.
Rearranged the table and referenced columns 1 and 2 for both two approximate and two exact matches. You have entered an incorrect track name for 2 of the formulas (& # 39; full love) & # 39;). If there was a matching row, I placed a VLOOKUP () statement next to the matching row.
You will see:
- If it is not an exact match and you enter FALSE as the fourth argument, the exact value is specified and the formula returns a # N / A error.
- If there is no exact match and the fourth argument is omitted or TRUE is used, VLOOKUP () will use the cell immediately above the first item is greater than the lookup value.
- If an exact match and an exact entry are specified, the first entry from the top is found.
- Location with exact match If you do not specify an exact value, the first match from the bottom is found.
- If you are curious why & # 39; Whole lotta love & # 39; is & # 39; greater than & # 39; Whole lot of love & # 39; For the next character, the letter t has a higher value when sorting the character than the space.
To be honest, most of the time you use VLOOKUP, you will be looking for an exact match. So you wonder what the gist of the rough game is. They can be very useful…
If you have a price tag and you need to check the value of a particular product on a certain date:
Our price list shows the date each new price was used. Since the bill date is not necessarily on that date, you need to find the latest date (the largest date) that is less than or equal to the bill date. List tables are sorted in ascending order.
You can use this example to see that HLOOKUP () is horizontally equivalent to VLOOKUP (). The third argument indicates which row to use, not which column.
VLOOKUP () and HLOOKUP () can be very useful, but sometimes you want to find data that is not organized from left to right or work with sorted values. Combining the descending MATCH () and INDEX () functions rather than ascending can help.
MATCH () and INDEX ()
There are many similarities to how VLOOKUP () and HLOOKUP () work when compared to MATCH () and INDEX (), but there are some important differences. .
VLOOKUP () and HLOOKUP () directly return the value of a cell. MATCH () uses a similar method to find cells that match a search function but returns a cell’s position in the cell list without returning a value. As you will see, this is why MATCH () is used with INDEX ().
You can also use an entire data table in a query and specify which columns or rows to use. MATCH () works with a single list or a simple list of single rows.
MATCH () and INDEX () Together
Match () is usually required to use a value to retrieve the contents of a cell. That’s why you need to combine MATCH () and INDEX (). There are two forms of INDEX (). Here’s a look at the format & # 39; array & # 39 ;. In this context, arrays mean blocks of cells without using Control + Shift + Enter to create the actual array formula. In the simplest case, you can use INDEX () and MATCH () to replace VLOOKUP () or HLOOKUP ().
INDEX () takes three arguments: a block of cells containing a table of values, a row number, and an optional column number that defines the cells within the block to return the value.
Let’s look at an example.
To find the value of the date 31/01/12, use:
= INDEX ($ E $ 3: $ F $ 7, B6, 2) Use as. You can include the MATCH () function inside the INDEX () function to do it all in one cell in the second column of the data table.
= INDEX ($ E $ 3: $ F $ 7, MATCH (A6, $ E $ 3: $ E $ 7), 2)
Simple! To find something next time, use VLOOKUP / HLOOKUP and INDEX MATCH to find the best one.
Teach all this much more in every Excel course.