Function Problems

Return a Type from Another Excel Sheet with INDEX and MATCH

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

Find the type associated with a name on a source worksheet and return it to another worksheet.

Return a Type from Another Excel Sheet with INDEX and MATCH
Product
Microsoft Excel
Device & OS
not provided
Scenario
The Source worksheet has types in column A and names in column B. Another worksheet contains names that need their matching types.
Observed behavior
An exact-match formula should return the corresponding type for each name on the other worksheet.
Before you start

Check that the Source sheet contains types in A2:A7 and corresponding names in B2:B7, and that the name to look up is in B2 on the destination sheet. Adjust the ranges in the formula if your data is elsewhere.

Solution 1Recommended

Use INDEX and MATCH to return the type

Use this formula when you need an exact name match across worksheets and want unmatched names to appear blank.

MATCH finds the position of the name in the Source sheet's name range, and INDEX returns the type at the same position. IFERROR displays a blank if the formula produces an error.

1
Select the result cell

On the destination worksheet, select cell A2 next to the name in B2.

2
Enter the lookup formula

In A2, enter =IFERROR(INDEX(Source!$A$2:$A$7,MATCH(B2,Source!$B$2:$B$7,0)),"") and press Enter.

3
Fill the formula down

Select A2 and drag its fill handle down column A to return types for the remaining names.

Use INDEX and MATCH to return the type
Duplicate names: MATCH returns the first matching name. If names are duplicated and require different types, add another condition to distinguish the records.
Work across worksheets in WPS

Look up types across sheets in WPS Spreadsheets

Use INDEX and MATCH in WPS Spreadsheets to find each name on a source sheet and return its type to another sheet.

  1. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and check the Source and destination sheet tabs.
  2. 2. Select the result cell: On the destination sheet, select A2 beside the name in B2.
  3. 3. Enter the formula: Enter =IFERROR(INDEX(Source!$A$2:$A$7,MATCH(B2,Source!$B$2:$B$7,0)),"") in A2 and press Enter.
  4. 4. Copy it down: Drag the fill handle from A2 down column A to calculate results for the other names.
Find associated types with an exact-match cross-sheet formulaOpen and edit Microsoft Excel XLSX and XLS filesFill the formula down for multiple names
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return a blank?

IFERROR returns a blank when the lookup produces an error. Check that the name in B2 matches a name in Source!B2:B7 and that both source ranges cover the correct rows.

What happens if the source contains duplicate names?

MATCH returns the position of the first exact match. Use another condition if duplicate names need to be matched to different types.

How do I include more source rows?

Edit both ranges in the formula to end on the same row. For example, change Source!$A$2:$A$7 and Source!$B$2:$B$7 to end at row 100.

How do I reference a source sheet whose name contains spaces?

Enclose the sheet name in single quotation marks in each reference, such as 'Source Data'!$A$2:$A$7 and 'Source Data'!$B$2:$B$7.