Some of the most popular Excel Lookup reference functions are VLOOKUP & HLOOKUP.
A newly added XLOOKUP is becoming very popular too. (XLOOKUP is currently only available in Office 365 versions). At Intellisoft, you can learn it by joining the XLOOKUP Training course in Singapore using Microsoft Office 365.
For the power users of Excel, the mastery of INDEX, MATCH & OFFSET can be considered vital, as these are considered the advanced lookup functions in Excel.
These functions will help you Analyze Data quickly. You should enrol in the data analysis and interpretation training class in Singapore.
But with the introduction of XLOOKUP, some of the jugglery created by mixing INDEX & MATCH combination is no longer required.
VLOOKUP Function of Excel
The most MUST HAVE Function ever. Even Excel gurus can’t live without it. I polled a group of Excel experts recently, asking if Excel’s VLOOKUP was overrated. I got a severe backlash for even mentioning it.
Almost everyone said that it is their GO TO function, an absolute must-have and that Excel won’t be that useable if this VLOOKUP function was taken away from Excel!
Most people swear by their VLOOKUP functions. It is their GO TO function when they want to lookup value of any type.
According to legend, VLOOKUP mastery is what separates the Pro Excel users from the Amateurs!
Vlookup is akin to using a dictionary. You know the word, and you want to find out the meaning. This dictionary is the range of cells that contain the lookup up value, and its associated value. The V in VLOOKUP stands for the dictionary being a vertical dictionary. So for a vertical lookup, you must use VLOOKUP function only.
=VLOOKUP(word, dictionary, column number of meaning, exact_match_ype)
The first column in the dictionary must contain the lookup up value, and the first row should be of the data. You should not include the headings in the dictionary table. The difficulty most people have with VLOOKUP is the last flag – the logical value of TRUE or FALSE (You can use 1 for True and 0 to indicate the False flag).
Once a matching value is found out, you will be able to get the return value based on the search. The error value of N/A will be generated if there is no exact match until the last row.
The mystery is created because to use VLOOKUP for an exact match, you have to specify the last optional flag, and set its value to a FALSE or a 0. By default, it is set to 1, which is useful for an approximate match type only. So for an exact match of a specific value, the last parameter is not really optional… it is mandatory.
VLOOKUP EXAMPLE:
There are a couple of major shortcomings in using VLookup function of Excel. First of all, the VLOOKUP is really a slow function. It is apparent when you do a lookup on a large list of 100,000 values or more. Secondly, VLOOKUP can only look up up a corresponding value from the columns on the right of the looked-up value. It can’t look to the left!
Make sure you master this Excel function really well.
HLOOKUP Function in Excel
An oft-forgotten cousin of VLOOKUP, this Horizontal Lookup and Reference function in Excel works in a similar way too. The only difference is that in this case, a lookup dictionary is a horizontal dictionary of columns, denoted by the H.
HLOOKUP is most used in range lookups, rather than exact matches, as columns are not the best suited for exact values, because of their limit of 16,000. Where a list can grow vertically to over a million records easily.
In the following formula, this lookup function searches for the closest match, especially when we are not searching for an exact match, but an approximate match. The dictionary is the table array and it is recommended that we use the absolute reference to lock the cells from moving.
=HLOOKUP(A5, $G$2:$K$100, 2)
Here the HLOOKUP will search for the exact or the next smallest value in the lookup table absolute range of $G$2 to $K$100, and return the second row. If you want the third row, you can change the 2 into a 3.
Both VLOOKUP & HLOOKUP return values from a single row or a single column.
Using the XLOOKUP Function in Excel
Did you know that new functions are added to Excel till today, and these are extremely useful functions making approximate matches as well as exact matches.
Finally, after years of backlash at Microsoft for creating the mess with the Match Type (True and False) in VLOOKUP, they got rid of it completely in the Excel XLOOKUP function.
And by default, XLOOKUP is set to do an exact match.
XLOOKUP requires a deeper understanding of the various scenarios. I’d recommend attending our formal ADvanced Excel Training to build a strong foundation in Excel. You can call us at 6250-3575 for more information of our courses and available enrollment dates for classroom training in Singapore.
This new XLOOKUP function of Excel is only available from Microsoft Office 365 users. It does not work on Excel 2016 or Excel 2019 versions.
Using INDEX Function in Excel
If you know the row number, you can find the value on that row or column cell directly.
INDEX can be used as an Array function also. Paired with MATCH, you can find any value on any row or column in a 2-dimensional array.
Index can help you to find the value on the row or the column of the specified number
How To Use Excel MATCH Function
When you want to find an exact match in an array and return the row number in the array, MATCH comes to your rescue. It is one up on VLOOKUP, which requires you to know the column you want to return. MATCH can find a match for a value that is lower, exactly equal or higher than the specified value.
Paired with INDEX, an INDEX & MATCH Function can manage to look up on the left or the right of any array of cells.
Master the OFFSET Function within Excel
To navigate your way in a two-dimensional array of rows and columns, you can use the OFFSET function in Excel. It can traverse any number of Rows or Columns, and get you the value.
How to use the offset function in Excel:
=OFFSET(Starting Cell, Row to move up or down, Columns to move left or right, Number of rows required to be returned, number of columns required to be returned)
I generally use OFFSET more than INDEX and MATCH combinations. Using one super-powerful OFFSET function is more straightforward.
Once you start using Offset in Excel, you wouldn’t want to use other lookup functions of Excel.
When Do I Use the INDIRECT Function of Excel?
The Excel INDIRECT function returns the reference specified by a text string. References are immediately evaluated to display their contents.
Use the INDIRECT function when you want to change the reference to a cell within a formula, without changing the formula itself.
=INDIRECT(A3)
The above Indirect function will check what is in cell A3. And A3 will have the cell reference to another cell. So if A3 contains B35, Excel will then read the value in cell B35.
Thus, we can get the value of the reference in cell A3. The reference is to cell B3, which may contain the value 45.
The INDIRECT can be very useful in creating custom management dashboards and reports.
What does the FORMULATEXT Function of Excel Do?
Displays the text of another formula. This helps to see all formulas next to their values and can be useful to spot mistakes and issues with formulas.
=FORMULATEXT(A3) will provide you with the formula in cell A3 as a Text Value.
This FormulaText function is useful to see the formula without having to go into Editing mode.
View this link for more information on how to get the Formula of another cell in Excel.
How to use ROWS Function of Excel
Displays the row number of a reference cell.
=ROWS(A1:B4)
Will return a 4. This is because there are 4 Rows in the given range.
How to Use the COLS Function in Microsoft Excel?
Displays the column number of a reference cell.
=COLS(A1:B4)
Will return a 2. This is because there are 2 Columns in the given range: A & B
Using the TRANSPOSE Function of Excel like a Pro
Converts rows into columns and columns into rows. Just like the Transpose feature in Paste Special, but done programmatically.
So if you use TRANSPOSE(A1:D3), you have selected 4 columns and 3 rows.
After the Transpose is completed, you will get an array reference of 3 Columns, and 4 Rows. The horizontal table would have flipped and will be visible vertically.
Pretty nice use of hanging values in rows into columns.
When Do I Use the UNIQUE Function of Excel?
The UNIQUE function of Excel generates a list of unique values that automatically spill down. An array function can be used to create data validation lists too. Available from Microsoft Office 365 onwards. This UNIQUE function is not available in Excel 2016 or Excel 2019.
Learning the Lookup Functions in Excel Quickly & Easily
As you can see, there are a lot of LOOKUP functions in Excel, and learning and mastering them takes time. But once you do master them, you can do wonders with your Excel skills.
It is worth the effort to learn the Excel Lookup Functions. Call Intellisoft at 6250-3575 or What’s App at +65 9066 9991 for Excel 365 Training that covers the key Lookup functions of Excel.
You will definitely enjoy it!
Cheers,
Vinai
Founder & Master Trainer at Intellisoft Systems in Singapore.