Excel INDEX Function: Chinese Data, Syntax, and Examples

Formulas & functions · Updated November 18, 2026

In Excel, INDEX returns a value from a specified position in a range, and it works with Chinese text just as it does with English text. With cities in A2:A4 and sales in B2:B4, =INDEX(B2:B4,2) returns the second sales value, 95; the function name stays INDEX even when the data or worksheet labels are Chinese.

What INDEX means in Excel

INDEX is a lookup and reference function. You give it a range and a row position, and, when the range has multiple columns, a column position too. Excel returns the content at that intersection. It does not search for a word by itself: it counts positions within the range you specify.

For example, position 1 means the first row of the selected range, not necessarily row 1 of the worksheet. If the range is B2:B4, its first, second, and third positions are B2, B3, and B4. This relative counting is the key to understanding INDEX and avoiding off-by-one mistakes.

INDEX can return text, numbers, dates, or other cell contents. The result may look like Chinese text because the source cell contains Chinese characters; INDEX does not translate or transliterate the value. It simply returns the selected cell’s content.

Using INDEX with Chinese Excel data

A Chinese-language workbook does not need a special version of the function. For Microsoft Excel for Microsoft 365 on Windows, the standard formula name is INDEX. Chinese help pages may describe it as the 索引 function, but that is a Chinese description of what it does, not a different function name to substitute in the formula.

Chinese characters can appear in the worksheet labels, lookup values, or returned results. For instance, a list can contain 上海, 北京, and 广州, and INDEX can return any of those values by position. Traditional and simplified characters are both handled as text. If a result seems not to match, check whether the source cells contain extra spaces or visually similar characters rather than assuming INDEX cannot read Chinese.

The examples below use commas between formula arguments, as in the standard Excel syntax. Some regional settings use semicolons instead; if Excel rejects a formula with commas, check the list separator configured for that installation. The underlying function and the order of its arguments are the same.

INDEX syntax and the two common forms

INDEX has an array form and a reference form. Most everyday lookups use the array form: select one rectangular range, specify a row number within it, and optionally specify a column number. The row and column numbers are positions inside that selected range, and counting starts at 1.

The reference form is useful when the first argument contains more than one separate range, or area. In that form, an area number can identify which range to use. It is less common in ordinary lookup sheets, so the array form is usually the simpler choice. For a single-column range, the row number is often all you need; for a two-dimensional block, provide both row and column positions.

A zero row or column argument can request an entire row or column from the selected array rather than one cell. In Microsoft 365, a formula that returns multiple values may spill into neighboring empty cells when the context supports dynamic arrays. If those cells are occupied, Excel can show a spill error. For a beginner’s lookup, use positive row and column positions to return one cell.

Simple examples with rows and columns

Suppose A1 contains 城市 and B1 contains 销售额. Cells A2:A4 contain 上海, 北京, and 广州, while B2:B4 contain 120, 95, and 110. With those values in place, the following formula returns 95 because Beijing’s sales value is the second item in B2:B4:

With sales values in B2:B4 as 120, 95, and 110, =INDEX(B2:B4,2) returns 95.

For a two-dimensional example, imagine a table with city names in A2:A4, quarter headings in B1:D1, and sales data in B2:D4. The rows represent 上海, 北京, and 广州; the columns represent 1季度, 2季度, and 3季度. The second row and third column of the data block intersect at Beijing’s third-quarter value, 118:

With B2:D4 containing rows 120, 135, 142; 95, 101, 118; and 110, 122, 130, =INDEX(B2:D4,2,3) returns 118.

These examples show why choosing the range carefully matters. If you include a header row in the array, the position numbers change. Select only the data cells when your row and column positions are meant to refer to records rather than headings.

Finding Chinese labels with INDEX and MATCH

INDEX is often paired with MATCH. MATCH finds the position of a requested item in a list; INDEX uses that position to return a value from a corresponding range. This combination separates the search value from the returned column and can look either left or right, unlike the traditional VLOOKUP layout requirement.

Using the city and sales table above, put 北京 in D2. MATCH finds that 北京 is the second item in A2:A4, and INDEX returns the second item from B2:B4. The final argument of MATCH is 0, which requests an exact match:

With city names in A2:A4, sales in B2:B4, and 北京 in D2, =INDEX(B2:B4,MATCH(D2,A2:A4,0)) returns 95.

You can also locate a value by both its row label and column heading. For example, put 北京 in F2 and 2季度 in G2. The first MATCH finds Beijing’s position among A2:A4; the second finds the quarter’s position among B1:D1. INDEX then returns the value at their intersection:

With cities in A2:A4, quarter headings in B1:D1, data in B2:D4, 北京 in F2, and 2季度 in G2, =INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(G2,B1:D1,0)) returns 101.

This approach is useful for reports that change shape over time. If a new quarter is added as another column, a formula can continue to identify the requested heading rather than relying on a fixed column number, provided the lookup range is extended to include it.

When to choose INDEX

Choose INDEX when the return position is known, when you need a flexible row-and-column lookup, or when you want to combine a search with MATCH. It is also useful in older workbooks because INDEX and MATCH have been available across many Excel generations. That makes the pair a practical option when a workbook may be opened on computers that do not have newer lookup functions.

  • Use INDEX alone when the position is already known, such as returning the third item in a selected list.
  • Use INDEX with MATCH when a label such as a Chinese city name determines which row to return.
  • Use a two-way INDEX/MATCH lookup when both a row label and a column heading determine the answer.
  • Prefer a simpler function when it clearly fits the job and the workbook’s Excel versions support it.

For Microsoft 365, XLOOKUP can often make a one-direction lookup easier to read because it combines the search range and return range in one function. INDEX and MATCH remain valuable for two-way lookups, compatibility, and existing models. The best choice depends on the workbook’s users and how much flexibility the sheet needs, not on whether its data is Chinese.

Common errors and how to fix them

A #REF! error usually means the requested row or column position falls outside the selected range, or a reference has become invalid. Check the range dimensions against the position numbers. If the array has three rows, a row position of 4 cannot return a cell from it.

A wrong but valid result is often an off-by-one error. Remember that INDEX counts from the top-left cell of its supplied array, not from the worksheet’s row and column labels. If the range starts at B2, position 2 refers to B3, not worksheet row 2.

If INDEX/MATCH returns #N/A, MATCH has not found an exact match. Check for leading or trailing spaces, spelling differences, and punctuation differences in the lookup value. Chinese words can contain full-width punctuation or characters that look alike but are not identical. A quick comparison of the lookup cell and source cell contents can reveal the difference.

Also make sure the lookup and return ranges have the same number of rows. If MATCH searches A2:A100 but INDEX is given B2:B20, a match found at position 25 has no corresponding position in the return range. Keep the ranges aligned, and use absolute references if you plan to copy the formula to other cells so that the ranges do not shift unexpectedly.

INDEX compared with other lookup options

VLOOKUP is familiar and can be convenient when the lookup column is at the left edge of a table and the return column is to its right. Its column-number argument can become fragile if columns are inserted or rearranged. INDEX with MATCH can return values from either side of the lookup column and makes the two ranges explicit.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, and is often the most straightforward choice for a basic exact lookup. INDEX itself is not a Chinese-specific alternative to either function. It is a position-based function that can be combined with MATCH, and it remains helpful when users need a two-dimensional lookup or compatibility with older Excel files.

None of these functions changes the underlying data. If a sales figure is stored as text, INDEX returns that text value; if a city name contains an extra space, INDEX returns it as stored. Clean and validate source data separately when consistent sorting, matching, or calculations matter.

Frequently Asked Questions

Is INDEX an Excel function in Chinese?
Yes. INDEX is the standard Excel formula name used to return a value by position. Chinese-language materials may call it 索引函数, but Chinese worksheet data does not require a different function.

Can INDEX return Chinese characters?
Yes. If the selected cell contains Chinese text, INDEX returns that text. It does not translate it, and it does not require the text to use simplified rather than traditional characters.

Why does INDEX return the wrong row?
INDEX counts from the first cell in the range you supplied. Check whether your range includes a header and whether your row number is relative to the range rather than the worksheet.

Do I need MATCH to use INDEX?
No. Use INDEX by itself when you already know the position. MATCH is optional and is useful when you want Excel to find a label’s position before INDEX returns the corresponding value.

Is INDEX better than XLOOKUP?
Not in every case. XLOOKUP is often simpler for a basic lookup in current Excel, while INDEX with MATCH can suit two-way lookups and workbooks that need broader version compatibility. Choose based on the workbook’s requirements.

Related

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.