Return a Type from Another Excel Sheet with INDEX and MATCH
Question details
Find the type associated with a name on a source worksheet and return it to another worksheet.

- 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.
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.
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.
On the destination worksheet, select cell A2 next to the name in B2.
In A2, enter =IFERROR(INDEX(Source!$A$2:$A$7,MATCH(B2,Source!$B$2:$B$7,0)),"") and press Enter.
Select A2 and drag its fill handle down column A to return types for the remaining names.

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. Open the workbook: Open the Excel workbook in WPS Spreadsheets and check the Source and destination sheet tabs.
- 2. Select the result cell: On the destination sheet, select A2 beside the name in B2.
- 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. Copy it down: Drag the fill handle from A2 down column A to calculate results for the other names.

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.




