VLOOKUP, HLOOKUP and XLOOKUP: one practical Excel example

I have a list of orders and a separate product list. Each order contains a product identifier, and I want to add the number of units available in stock. Copying those values manually would turn every update into another round of repetitive work. I would rather describe the relationship once: find this identifier in the product list and return the value from the corresponding row.

Lookup functions handle that relationship. The interesting part starts when an identifier is missing, appears twice or looks like a number while actually being stored as text. Excel can follow a poorly specified instruction very faithfully. Seeing a number in the result cell is not enough to convince me that the answer is correct.

I will use one small, synthetic example throughout this article. First I will make the formulas work. Then I will deliberately introduce awkward cases. The aim is to understand both the function and the checks that make its output useful. None of the stock quantities represents a real business.

Check function names and Excel versions first

The English function names are VLOOKUP, HLOOKUP and XLOOKUP. In Polish Excel they are WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO and X.WYSZUKAJ. I use English names and commas in this version of the article. Your regional settings may require semicolons instead. A syntax issue caused by language or separators does not mean the product data needs changing.

I also check the version used by the person receiving the workbook. Microsoft’s XLOOKUP documentation says that the function is unavailable in Excel 2016 and Excel 2019. It is available in versions including Microsoft 365, Excel 2021 and Excel 2024. A formula working on my computer does not establish compatibility with every machine in a company.

For a new workbook with compatible recipients, I usually choose XLOOKUP. The older functions remain worth understanding because they appear in existing reports. Their limitations also make the connection between a formula and the shape of its data easier to see. I would not rewrite a reliable report solely to replace an older function name with a newer one.

Build a product list and one order key

Enter the following data in A1:C5 of a new worksheet. The first row contains the headings. In this exercise, each product should have exactly one identifier and one stock quantity.

Enter P02 in F2. This cell represents the product identifier on one order line. For now, I only want to retrieve its recorded stock quantity. I am not deciding whether the warehouse can fulfil every pending order. That would also require requested quantities and a rule for allocating stock between orders.

Keeping those questions separate matters. Looking up stock retrieves a value attached to a product. Deciding whether to fulfil an order requires additional analysis. If I mix the two tasks, I could accidentally promise the same seven units to several customers at once.

Before writing any formula, I check the small table manually: P02 occurs once, and its stock is 7. That gives me a known answer to compare with each formula. I want to understand one result before copying the calculation into thousands of rows. A tiny test case is much easier to inspect than a large workbook full of plausible numbers.

VLOOKUP searches the first column

Enter this formula in an empty cell. With the original data, the expected result is 7.

=VLOOKUP(F2,$A$2:$C$5,3,FALSE)

F2 supplies the identifier. The table range contains three columns. The number 3 selects the third column within that range, which holds stock. The final argument requests an exact match. VLOOKUP searches the first column of the supplied range, so the identifiers belong in column A for this particular layout.

The dollar signs keep the lookup range fixed when I copy the formula. F2 remains relative, allowing it to become F3, F4 and subsequent keys when copied down. Without that distinction, the lookup range could move along with the formula and gradually exclude products near the top of the list.

The argument I pay most attention to is FALSE. Omitting the fourth argument selects approximate matching, which solves a different problem and requires the appropriate ordering. For a product identifier, I want P02 itself. I do not want a nearby entry that happens to produce a convenient number. The argument rules are described in Microsoft’s VLOOKUP documentation.

XLOOKUP separates the key range from the return range

Now I express the same task with XLOOKUP. The expected result remains 7, but I identify the search range and the return range separately.

=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"Missing ID",0)

The zero explicitly requests an exact match, even though exact matching is already the default. The fourth argument supplies a message when the identifier is absent. I deliberately avoid returning zero for a missing product because zero already has a legitimate meaning: it is the stock quantity recorded for P03.

Both ranges contain four cells and correspond by position. A match in the second position of A2:A5 returns the second position of C2:C5. I check that relationship whenever someone has rearranged the source. A well-written formula cannot repair columns that no longer describe the same products because one column was sorted independently.

If I leave out the missing-value message, an unmatched identifier produces #N/A. That error is useful evidence. Sometimes the honest answer is that the information required for the task is unavailable. Hiding the error before investigating it can make the workbook easier to look at while making the report less trustworthy.

Looking left and changing the column layout

Enter Mouse in F3. This time I want its identifier, which is stored to the left of the product name. Names happen to be unique in this small example.

=XLOOKUP(F3,$B$2:$B$5,$A$2:$A$5,"Missing name",0)

The result is P02. XLOOKUP lets me place the return range on either side of the search range. A basic VLOOKUP returns a column within its table range, counting from the first column where it searches. That makes XLOOKUP more convenient for this reverse question.

In a real model, I would still prefer a stable identifier as the key. Product names can change, repeat or acquire extra descriptive text. This example demonstrates lookup direction. It is not a recommendation to treat every product name as a permanent identifier.

I also test what happens when a column is inserted into the source table. VLOOKUP contains the literal number 3, which may point to a different field after the layout changes. XLOOKUP refers directly to its return range, so an ordinary column insertion updates that reference. I still verify the result after structural changes. Moving values independently or sorting only one column can break the relationship used by either function.

HLOOKUP applies the same idea across a row

Some reports arrange products horizontally. To reproduce that layout, put the identifiers in A9:D9 and their stock quantities in A10:D10. Keep P02 in F2.

=HLOOKUP(F2,$A$9:$D$10,2,FALSE)

The answer is 7 again. HLOOKUP searches the first row of the supplied range and returns a value from its second row, in the matching column. The row index is relative to the range, rather than the worksheet’s row numbering. Microsoft documents HLOOKUP separately from its vertical counterpart.

I do not reorganise a dataset merely to use a particular function. When designing a product list from scratch, however, I usually put one product on each row. That makes adding records and inspecting identifiers straightforward. A horizontal arrangement may be a useful report view without being the most convenient way to maintain the underlying data.

Missing products, zero stock and duplicate keys

Change F2 to P99. VLOOKUP now returns #N/A, while the XLOOKUP formula displays “Missing ID”. Next, enter P03 and observe the valid zero. These outcomes call for different actions. A missing identifier requires investigation of the order or the product list. Zero says that the product exists and its recorded stock is zero.

Restore P02, then deliberately introduce a duplicate: change A5 to P02 and C5 to 99. The default lookup returns the first matching occurrence, giving 7. To search from the end, I can supply minus one as the final argument.

=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"Missing ID",0,-1)

This version returns 99. It does not automatically return the latest stock value, because row position is not a date. Searching backwards only represents a historical rule when I have defined and maintained the relevant ordering. In a plain product dictionary, I would treat the second P02 as a data-quality problem.

=COUNTIF($A$2:$A$5,F2)

For our simple identifier, the count is now 2. This check distinguishes one matching entry from several. Identifiers containing wildcard characters need additional care because COUNTIF interprets its criterion. Our keys contain only a letter and digits. After this experiment, restore P04 in A5 and 5 in C5 so that the remaining exercises use the original list.

Approximate matching belongs with thresholds

Approximate matching is useful when assigning an order to a size band. Enter the following thresholds in A14:B17, without an extra header row inside that range. Put 37 in F4 to represent an example order quantity.

=VLOOKUP(F4,$A$14:$B$17,2,TRUE)

=XLOOKUP(F4,$A$14:$A$17,$B$14:$B$17,"Missing threshold",-1)

Both formulas return “Medium”: 37 lies between 10 inclusive and 50 exclusive. The VLOOKUP version requires the thresholds in ascending order. In the XLOOKUP version, minus one requests an exact match or the next smaller value. This match-mode argument has a different purpose from the final search-direction argument in the previous example.

I test the boundaries explicitly. A quantity of 10 belongs to Medium, and 50 belongs to Large. A value of minus one has no qualifying lower boundary. A value of 150 belongs to Bulk because this model leaves the final band open at the top. If the business requires a maximum permitted quantity, that needs a separate range check.

Using match mode 1 in XLOOKUP would select an exact match or the next larger value. For 37, that would select 50. Such behaviour can make sense for a table of upper boundaries, but it does not match our table of lower boundaries. I describe the intervals in ordinary language before choosing the function argument.

When matching-looking keys fail to match

For the next experiment, I use a copy of the practice worksheet. I store the number 2 in one cell and the text “2” in another. They may look identical while remaining different values for an exact lookup. I inspect the actual types and the import process instead of treating cell alignment as proof.

I do not convert every identifier to a number. A code such as 002 may carry a different meaning from 2. For identifiers, I agree on a consistent representation with the data owner and apply it on both sides of the lookup. A quantity imported as text usually needs a different correction from a product code intentionally stored as text.

Spaces deserve the same care. P02 with a trailing space can fail to match P02 without it. TRIM helps with ordinary spaces, but it is not a universal cleaning tool. Microsoft explicitly notes that TRIM does not remove a nonbreaking space by itself. I identify the character before choosing a transformation.

I also avoid wrapping the entire calculation in broad error handling before understanding the cause. “Missing ID” is helpful when the identifier is absent. Displaying that message for a broken reference would point the reader towards the wrong repair. The purpose of a report is to support useful decisions, including decisions about fixing the source data.

Copying the formula is part of its logic

Once the first formula works, I prepare a separate area with a few more order lines: P01, P02, P03 and P99. I expect 12, 7, 0 and a missing-product message respectively. Only then do I extend the calculation to the full file. This is especially useful when adapting a formula written by someone else or copied from an older report.

I inspect every part of the reference. Locking a column and locking a row are separate choices. Here I lock the entire dictionary because the formula moves down an order list. A report copied both down and across may need different references. Instead of cycling reference styles mechanically, I describe which parts must remain fixed after the formula moves.

I also look for formulas that someone replaced with a manually entered number. A quick correction can survive several refreshes, leaving some rows connected to the latest source and others displaying old values. For recurring work, I separate inputs from calculations and explain where corrections belong. A stock snapshot needs its date too: recalculating a lookup does not fetch a new delivery from a warehouse system. The source must be updated first.

Check the whole relationship before sharing the report

After copying the formulas, I inspect several known products and every unusual result. I count unmatched identifiers and ambiguous keys. I compare the number of order lines before and after adding the lookup column. Retrieving a product attribute should not add or remove orders.

I check the lookup range too. Adding P05 below the final row will not extend a formula that still stops at A5. For a recurring report, I consider an Excel table with structured references or another controlled way to extend the range. First, I establish who adds products and how the report will detect new records. A function cannot invent an update procedure.

Before handing over the workbook, I add a short explanation of the source, the meaning of stock, the refresh date and the treatment of missing keys. The reader needs to know whether the numbers describe the current position or a snapshot from an earlier date. Perfect matching can still retrieve outdated information.

The examples in this article were checked in a local Microsoft Excel installation, including missing keys, zero, duplicates, thresholds and number-versus-text mismatches. That establishes the results for the data shown here. I still retest a real workbook after changes to its source, structure or software version. For larger table combinations, understanding SQL relationships and JOIN types is useful too: key uniqueness matters across tools.

My suggested next exercise is simple. Add an order containing a nonexistent product, then add a second occurrence of an existing product to the dictionary. Explain each result and identify who should correct the source. Once you can do that, you understand more than the function’s syntax. You understand the relationship that the formula is supposed to represent.