Excel HLOOKUP: Return a Column Number

HLOOKUP does not return the number of the column where it finds a match; it returns a value from a row you specify. To get the match’s position within a horizontal range, use MATCH with an exact match, such as =MATCH("March",B1:E1,0), which returns 3 when March is the third item in that range.

What “return a column number” can mean

The question can refer to two different results: the position of a match in a lookup range, or a value found in a worksheet column. Those are not the same. MATCH returns a position relative to the range you give it. HLOOKUP searches the top row of a table and returns a value from a specified row beneath that top row.

For example, if the lookup row is B1:E1, the second item in that range is in worksheet column C, but its relative position is 2. If you want the relative position, MATCH is usually the right tool. If you want the actual worksheet column number, such as 3 for column C, use COLUMN with a reference to the matched cell.

There is also a third possibility: you may want HLOOKUP to return a number that happens to be stored in the table. HLOOKUP can do that, just as it can return text or a date, as long as the number is in the row selected by its row-index argument.

How HLOOKUP works

HLOOKUP stands for horizontal lookup. It searches across the first row of a rectangular table for a value. Once it finds a match, it moves down within the table by the number of rows you specify and returns the value at that position.

Its arguments are the value to find, the table range, the row index number, and an optional match setting. The row index counts from the top of the table: 1 means the first row, 2 means the second row, and so on. This is a row number within the selected table, not a worksheet column number and not the position of the match across the top row.

With product names in A1:D1 and prices in A2:D2, HLOOKUP can find a product name in the first row and return its price from the second row. It does not report which column contained the name. That distinction is the source of much of the confusion around the phrase “HLOOKUP return column number.”

Use MATCH to return a position

MATCH is designed to find the position of a value in a one-dimensional range. For a horizontal lookup, give it a row range. The third argument set to 0 requests an exact match, which is generally the appropriate choice for labels such as months, product codes, or names.

With month names in B1:E1, in that order as January, February, March, and April, =MATCH("March",B1:E1,0) returns 3. The number counts from the beginning of B1:E1, so it does not return March’s worksheet column number. It returns the third position in the supplied range.

To look up a label stored in a cell rather than typing it into the formula, with the month names in B1:E1 and the desired month in G1, use =MATCH(G1,B1:E1,0). If G1 contains March, the result is 3. This version is easier to reuse: change G1 to April, for instance, and the result becomes 4.

MATCH returns the position of the first exact match it encounters. If the lookup row contains the same label more than once, it does not distinguish between those duplicates; it returns the first one from left to right. If the value is not present, an exact-match lookup returns the #N/A error.

Return the actual worksheet column number

A relative position and an absolute worksheet column number only coincide when the lookup range starts in column A. To convert the position to the worksheet’s column number, add the starting column number and account for the fact that the first position is offset by zero.

With month names in B1:E1 and the target month in G1, =COLUMN(B1)+MATCH(G1,B1:E1,0)-1 returns 4 when G1 contains March. The arithmetic is 2 + 3 − 1: B is worksheet column 2, and March is the third item in the range. The result, 4, identifies worksheet column D.

Another option is to use INDEX to refer to the matched cell and COLUMN to report that cell’s worksheet column. With month names in B1:E1 and the target month in G1, =COLUMN(INDEX(B1:E1,1,MATCH(G1,B1:E1,0))) also returns 4 for March. This can be useful when you want the formula to express directly that the result is the column containing the match.

Use the relative position when you are going to use it as an index within the same range. Use the worksheet column number when another calculation or report needs the actual column location. Mixing them up can lead to an off-by-one error, especially when the lookup range begins somewhere other than column A.

How the HLOOKUP row index differs

HLOOKUP’s third argument is often called a row index number. It selects how far down the table to return a value; it does not select the matched column. For example, with months in B1:E1, sales in B2:E2, and expenses in B3:E3, a row index of 2 returns sales from the column where the month matches, while a row index of 3 returns expenses.

With month names in B1:E1, sales in B2:E2, and the requested month in G1, =HLOOKUP(G1,B1:E2,2,FALSE) returns the sales value under that month. If G1 contains March and the value in D2 is 450, the result is 450. The 2 tells HLOOKUP to return from the second row of B1:E2; it does not mean “return column 2.” FALSE asks for an exact match.

When possible, include the optional match setting explicitly. With FALSE, HLOOKUP looks for an exact match. With TRUE, or when that argument is omitted, it uses an approximate match and expects the first row to be sorted in ascending order. An approximate match can produce an unexpected result if the row is not sorted as required.

Use MATCH and HLOOKUP together

Sometimes the goal is to return a value from a horizontal table while also choosing which row to return based on another label. MATCH can locate the row number for HLOOKUP’s third argument, provided the row label is arranged appropriately in the lookup table. For a two-way lookup with labels across the top and down the first column, INDEX and two MATCH functions are often clearer because they separately find the row and column positions.

For example, suppose B1:E1 contains months, A2:A4 contains department names, and B2:E4 contains the data. With the requested month in G1 and department in G2, =INDEX(B2:E4,MATCH(G2,A2:A4,0),MATCH(G1,B1:E1,0)) returns the value at the intersection. If G2 is Support and G1 is March, it finds Support’s row and March’s position, then returns the value at that intersection.

This pattern is useful because MATCH supplies relative positions within the relevant ranges, and INDEX uses those positions to return the intersecting value. The formula does not rely on counting a fixed number of rows or columns by hand. Ensure that the data range begins immediately below and to the right of the label ranges, as in this layout, so the positions line up.

Common errors and practical checks

If a MATCH formula returns #N/A, the lookup value may be absent, or the value and lookup row may differ in a way that is not obvious. A number stored as text, an extra space, or a slightly different spelling can prevent an exact match. Check the source values and make sure the lookup range contains the intended labels.

Confirm the lookup range is a single row for a horizontal match, such as B1:E1.
Use 0 as MATCH’s third argument when you need an exact match.
Check whether duplicate labels exist; MATCH returns the first one.
For an absolute worksheet column, account for the lookup range’s starting column instead of treating the relative position as absolute.
For HLOOKUP, make sure the row index does not exceed the number of rows in the selected table.

HLOOKUP can return #REF! if its row index asks for a row below the bottom of the table array. It can return #N/A if it cannot find the lookup value under the selected match behavior. A wrong but plausible result can occur when approximate matching is used on an unsorted first row, so specify FALSE when exact matching is intended.

Choosing between HLOOKUP, MATCH, and alternatives

Choose MATCH when you only need the position of a label in a horizontal range. Choose HLOOKUP when you need to search across the top row and return a value from a specified row beneath it. Use INDEX with MATCH when you need a more flexible lookup, such as returning the value at the intersection of a row label and a column label.

In Microsoft 365 for Windows, XLOOKUP is another option for many lookup tasks. It can search horizontally as well as vertically and lets you specify a separate return range, so you do not have to count a row index within a larger table. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older Excel releases. If a workbook must work in older versions, MATCH, INDEX, and HLOOKUP are more broadly compatible choices.

For a simple horizontal lookup in an older workbook, HLOOKUP remains valid and familiar. For a position-only result, MATCH is more direct. Choose based on what the formula needs to produce: a relative position, an absolute worksheet column, or a value from the table.

Frequently Asked Questions

Can HLOOKUP return the column number where it finds a match?
Not by itself. HLOOKUP returns a value from a selected row in its table. Use MATCH to return the match’s position in a horizontal range, or combine MATCH with COLUMN if you need the actual worksheet column number.

Does MATCH return a column number or a position?
MATCH returns a position relative to the lookup range. If the range is B1:E1 and the match is in D1, MATCH returns 3, while D is worksheet column 4.

What does the third HLOOKUP argument mean?
It is the row index number within the table array. A value of 2 tells HLOOKUP to return from the table’s second row, not from its second worksheet column.

Why does MATCH return #N/A?
With an exact match, #N/A means Excel cannot find the lookup value in the specified range. Check for missing labels, extra spaces, spelling differences, or a number-versus-text mismatch.

Can I use MATCH with a horizontal range?
Yes. MATCH works with a single row or a single column. For a horizontal lookup, select the row containing the labels and use 0 as the third argument when you want an exact match.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.