Free Excel workbook
Get the VLOOKUP & HLOOKUP Workbook
Download the ready-to-use workbook and practice the exact formulas from the tutorial.
- Practice VLOOKUP and HLOOKUP with the same examples from the tutorial
- See exact match, wildcard, and lookup direction examples already set up
- Follow the tutorial without building the workbook from scratch
Enter your email below for instant access.
Free. Instant access.
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.
When should I use VLOOKUP instead of HLOOKUP?
The decision is purely about how your data is laid out. Use this quick rule:
| Your Data Layout | Use This Function |
|---|---|
| Lookup value is in the leftmost column, returned values are in columns to the right | VLOOKUP |
| Lookup value is in the top row, returned values are in rows below | HLOOKUP |
| Lookup value can be in any column or row, returned values can be in any direction | XLOOKUP (Microsoft 365 and Excel 2021+) |
Real-world examples
- VLOOKUP example: A customer master list with one customer per row. Customer ID in column A, name in column B, region in column C. Look up the region by customer ID.
- HLOOKUP example: A monthly forecast with months as headers across row 1 (Jan, Feb, Mar…) and revenue figures in row 2. Look up the revenue for “April.”
If your data could go either way, organize it so the lookup column is the leftmost (and use VLOOKUP). Vertical layouts are more common in business spreadsheets and easier to maintain as your data grows.
What are the common VLOOKUP and HLOOKUP errors?
Both functions throw the same handful of errors for the same reasons. Here are the four you’ll hit most often and how to fix each.
#N/A error
The lookup value doesn’t exist in the search range. Common causes:
- Extra spaces. Wrap your lookup value in TRIM:
=VLOOKUP(TRIM(A2), table, 2, FALSE) - Number-vs-text mismatch. Numbers stored as text don’t match real numbers. Use Text to Columns to convert text to numbers.
- Typo in the lookup value or table data. Compare letter by letter.
- Table range shifted when you copied the formula. Lock the table with
$signs:$D$2:$F$100.
#REF! error
The col_index_num (or row_index_num for HLOOKUP) is greater than the number of columns (or rows) in your table. Example: a table with 3 columns and you asked for column 4. Fix the index number.
#VALUE! error
The col_index_num is less than 1 or contains text. The third argument must be a positive whole number.
#NAME? error
The function name is misspelled. Check that you typed VLOOKUP or HLOOKUP correctly with no spaces.
💡 Pro tip: Wrap your lookup in IFERROR to show a friendly fallback: =IFERROR(VLOOKUP(A2, table, 2, FALSE), "Not found"). This replaces all error types with whatever message you want.
Should I still use VLOOKUP and HLOOKUP in 2026?
If you have Microsoft 365 or Excel 2021 and later, the answer is no. XLOOKUP replaces both VLOOKUP and HLOOKUP and fixes the most painful limitations:
- One function for both directions. XLOOKUP works horizontally and vertically. No more remembering which is which.
- Looks left or right. The lookup column doesn’t have to be on the left like with VLOOKUP.
- Defaults to exact match. No more forgetting the FALSE argument.
- Built-in not-found handling. Replace IFERROR wrappers with the fourth argument:
=XLOOKUP(A2, names, sales, "Not found"). - Approximate match without sorting. VLOOKUP requires sorted data for approximate match. XLOOKUP doesn’t.
When VLOOKUP and HLOOKUP still make sense
- Backward compatibility. If your file needs to open in Excel 2019, 2016, or earlier, XLOOKUP isn’t available.
- Working with someone else’s existing formulas. Don’t rewrite working VLOOKUPs just because XLOOKUP exists. The risk of introducing bugs outweighs the elegance gain.
- Learning the foundations. Understanding how VLOOKUP and HLOOKUP work makes you better at troubleshooting any lookup problem, including XLOOKUP errors.
For a full XLOOKUP walkthrough, see our XLOOKUP function tutorial. For a direct side-by-side, see our XLOOKUP vs VLOOKUP comparison.
Can Copilot do lookups instead of VLOOKUP?
Yes. With Edit with Copilot in Microsoft 365, you can describe what you want to look up in plain language and Copilot writes the formula for you. It usually picks XLOOKUP since that’s the modern default, but you can ask for VLOOKUP or HLOOKUP specifically.
Example prompts
“Look up the sales for each customer ID in column A from the master list on the Customers sheet.”
“Find the price for product code in B2 from the Pricing tab using VLOOKUP.”
“Pull the regional manager name for each region listed in column C.”
Copilot handles common pitfalls automatically: it locks the table array with $ signs, sets the match mode to exact, and wraps with IFERROR if you ask for not-found handling.
Requirements
- Subscription: Microsoft 365 Personal, Family, or Premium with AI credits; or a Microsoft 365 Copilot commercial license
- File location: Workbook must be .xlsx on OneDrive or SharePoint
- Version: Microsoft 365 desktop or Excel for the web
If you don’t have Copilot, the syntax in this guide covers every case manually. Copilot just removes the friction for ad-hoc lookups.
How can I extend VLOOKUP with wildcards, math, and nested formulas?
How do I use wildcards with VLOOKUP? (Asterisk and question mark)
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.
Wildcard match for an exact-length text (???)
=VLOOKUP(" ??????? ", A1:C9, 2, FALSE)Wildcard match for a single unknown character (?)
=VLOOKUP("ast?03", A1:C9, 2, FALSE)Wildcard match for any text containing a word (*)
=VLOOKUP("*ast*", A1:C9, 2, FALSE)How do I do math with VLOOKUP results?
Other operations can be performed on VLOOKUP formula results such as multiplying or dividing two VLOOKUPS with each other.
How do I nest VLOOKUP inside other functions?
You can also use “nested” formulas with the IF function.
What are the limitations of VLOOKUP and HLOOKUP?
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: If you need to look up data in any direction, the INDEX and MATCH combination is much more flexible than VLOOKUP. See our INDEX and MATCH guide for the full walkthrough.
VLOOKUP vs HLOOKUP FAQ
What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches vertically down the leftmost column of a table and returns a value from a column to the right. HLOOKUP searches horizontally across the top row and returns a value from a row below. Use VLOOKUP when your data is organized in columns. Use HLOOKUP when it’s organized in rows.
When should I use HLOOKUP instead of VLOOKUP?
Use HLOOKUP when your lookup value is in the top row of a table and the data you want to return is in a row beneath it. This is common with monthly headers like Jan, Feb, Mar across the top of a spreadsheet, or quarterly columns where each quarter is a heading row.
What is the syntax of VLOOKUP and HLOOKUP?
VLOOKUP syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Both have the same parameters except the third one: VLOOKUP uses a column number, HLOOKUP uses a row number.
Why is my VLOOKUP returning #N/A?
The most common causes are: the lookup value doesn’t exist in the leftmost column, extra spaces in either the lookup value or the table (use TRIM), the table_array isn’t locked with $ signs and shifted when you copied the formula, or the data type doesn’t match (numbers stored as text won’t match real numbers).
Should I still use VLOOKUP and HLOOKUP in 2026?
If you are on Microsoft 365 or Excel 2021 and later, use XLOOKUP instead. It replaces both VLOOKUP and HLOOKUP, works in any direction, defaults to exact match, and has built-in error handling. Stick with VLOOKUP and HLOOKUP only if you need backward compatibility with Excel 2019 or earlier.
Can VLOOKUP look to the left?
Not directly. VLOOKUP can only return values from columns to the right of the lookup column. To do a left lookup, either rearrange your data, use INDEX and MATCH together, or use XLOOKUP which works in any direction natively.
Download the Practice Workbook
Free Practice Workbook
VLOOKUP, HLOOKUP, and XLOOKUP examples ready to test.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
