For two of our formulae, we have entered an inexact track name (‘Whole lot of love’). Here, we have re-sorted our table and used two approximate matches, and two exact matches, all referring to columns 1 and 2. If there is no exact match, it will find the first item in our table larger than the lookup value and match with the cell immediately above.įor this reason, whenever you don’t specify an exact match by using FALSE as the fourth argument in VLOOKUP(), you must ensure that your table of data is sorted in ascending order, using the leftmost column. In fact, it’s really even more specific than that. It finds the largest value in our table that is smaller than, or equal to, our lookup value. It is actually a much more specific type of match than that: The reason for this is that the approximate match doesn’t mean find the ‘closest’ value to our lookup value wherever it is in the table. If we changed our column index to 1, we would find that Excel had matched ‘Whole Lotta Love’ with ‘Honky Tonk Women’. In fact, a Rolling Stones album that certainly doesn’t include Led Zeppelin’s ‘Whole Lotta Love’. Although there are several exact matches for our value in our table, the function now returns a completely different album title. Perhaps we don’t know about the fourth argument or we aren’t sure about the spelling of ‘Lotta’ so we think we’ll use an approximate match rather than an exact match:Īll we have changed in our formula is to omit the fourth argument. The use of the optional, fourth argument. Let’s now look at an even more important issue with the lookup functions. You would either need to filter the table or use a PivotTable. If we need to return multiple results from a table then the lookup functions are unlikely to work. As we can see, there is another match further down that VLOOKUP() has ignored. Let’s take a look at how a VLOOKUP() works:Įven this simple example shows one of the issues of the lookup functions: they only return the first match. The VLOOKUP() function assumes that your data is arranged as a table with different elements of the information in different columns.
Where is vlookup in excel 2016 how to#
The V and H in the names of these two functions refer to Vertical and Horizontal respectively, so the good news is that once you’ve learnt how to use VLOOKUP(), HLOOKUP() should be easy, as it works in exactly the same way, but with data arranged in rows as opposed to columns. Although the Excel lookup functions can seem quite straightforward, it’s very easy to get the wrong answer if you don’t fully understand how they work.Įxcel does have an additional lookup function: LOOKUP() but this is only included for compatibility with older spreadsheet applications, so we’ll concentrate on VLOOKUP() and HLOOKUP().
0 kommentar(er)
