Should I use VLOOKUP or HLOOKUP?
If the data in your table has column headers, use the VLOOKUP function. If the data table has row headers and you need to search horizontally across the rows for a “match,” then use HLOOKUP.
The main limitation is that the value looked with VLOOKUP needs to be in the left-most table column and in HLOOKUP it has to be in the top row of the data set.
Below is an example of the less commonly used HLOOKUP function.
HLOOKUP is used to search along the top row and then look down to retrieve the result from a specified row – the second row in this instance.
The example below uses the VLOOKUP function.
The VLOOKUP formula syntax is the following
- Lookup_value: A11 or East_01 (in this example).
- Table_array: the range includes the location of the column lookup value and the columns where the return value is located (i.e. A1:B9).
- Col_index_num: the number of columns it should move to the right. In this case, the range contains two columns with the match found in the second column.
- Range_lookup: this argument is important to use correctly if the lookup value is text and non-numeric. If the parameter is left empty, it defaults to “TRUE” meaning that an exact match is unnecessary. But, to work properly, the lookup column must be sorted in ascending order. Otherwise, an error or #NA will appear in the cell if the column isn’t sorted in ascending order and this argumentis left empty. In the example above, if FALSE is excluded from the last argument then the formula will return an #NA error, because the lookup column is not sorted in ascending order. (Text shouldn’t include trailing spaces). Use the trim function to “clean up” your text to avoid this from happening.
Featured Course
Excel Essentials for the Real World
Building up on the VLOOKUP Function
Wild Cards: Various wild cards can be used with VLOOKUP. You have the option to use the asterisk (*) to match any sequence of characters or the question mark (?) to match any single character. Note that if you want to find an actual question mark or asterisk type a ~ before the character.
Returns the value for the first 7 characters.
Returns the match value for the first “ast” in the name.
Uses both “?” and “*” to search for the first match of a text.
Manipulation: Other operations can be performed on VLOOKUP formula results such as multiplying or dividing two VLOOKUPS with each other.
Nesting: You can also use “nested” formulas with the IF function.
Limitations of VLOOKUP
One major limitation of VLOOKUP is that it cannot look to the left. The values to lookup must always be on the left-most column of the range and the values to return must be on the right hand side.
You cannot use the standard VLOOKUP to look at the columns and the rows to find an exact match. The INDEX and MATCH functions are used in this instance and are much more flexible than the classical VLOOKUP.
Tip: Although the CHOOSE function nested in VLOOKUP is a “workaround” tool, the Index and Match combination is more effective to look data up in any direction.
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
