Use INDEX and MATCH in WPS Spreadsheet to Return Duty and Location Values
Question details
Retrieve duty and location values in a log by matching each person's name and day against worksheet tables.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants a two-way lookup that finds the correct row by person name and the correct column by day, including data stored on another worksheet.
- Observed behavior
- The required result is the value at the intersection of the matched person row and matched day column.
Check that the person names and day headers use the same text in both the log and source tables. Day names must match exactly, including spelling, spaces, and abbreviations such as "Mon" versus "Monday".
Use INDEX with two MATCH functions for a two-way lookup
Use this formula when names are listed down the first column of a table and days are arranged across the header row.
INDEX returns the value where the matched name row and matched day column intersect. Add the worksheet name before each source range when the lookup table is on another sheet.
In the log worksheet, click the cell where the duty or location result should appear, such as the result cell on the same row as the person's name and selected day.
Type a formula such as =INDEX(DutyTable!$B$8:$F$9,MATCH(D13,DutyTable!$A$8:$A$9,0),MATCH(B13,DutyTable!$B$7:$F$7,0)). Replace DutyTable with the source worksheet name and adjust the ranges to match your table.
Press Enter. The first MATCH finds the row containing the name in D13, and the second MATCH finds the day header matching B13.
Drag the fill handle from the result cell down or across the log. Keep the source table ranges absolute with $ signs so they do not shift while copying.
Correct name and day text that prevents an exact match
Use this when the formula returns #N/A even though the name or day appears to be present in the source table.
Build Cross-Sheet INDEX and MATCH Lookups in WPS Spreadsheet
WPS Spreadsheet lets you create two-way INDEX and MATCH formulas to return duties, locations, schedules, and other values by matching a person and day across worksheets. Use familiar spreadsheet formula entry and fill tools to apply the lookup throughout your log.
- 1. Open the log workbook: Open the workbook in WPS Office and select the worksheet containing the person-and-day log.
- 2. Identify the source table ranges: On the source worksheet, identify the result range, the name column, and the day-header row to use in the INDEX and MATCH formula.
- 3. Insert the INDEX and MATCH formula: Click the destination cell and enter INDEX with one MATCH for the name row and one MATCH for the day column, using 0 for exact matches.
- 4. Verify a sample result: Press Enter and compare the returned duty or location with the matching row-and-column intersection in the source table.
- 5. Fill the formula through the log: Use the fill handle to copy the formula to additional rows while retaining absolute references for the source ranges.
Frequently Asked Questions
How do I return a value from a different worksheet with INDEX and MATCH?
Prefix each source range with the worksheet name, such as DutyTable!$B$8:$F$9. Use INDEX for the return table and two MATCH functions to find the correct name row and day column.
Why does my INDEX and MATCH formula return #N/A for a day?
The day in the log must exactly match a header in the source table. Check spelling, abbreviations, spaces, and whether one cell contains text while the other contains a date value.
Can I use the same formula to return both duty and location?
Yes. Keep the MATCH functions for the person and day unchanged, then replace the INDEX return range with the duty table range or location table range you want to return.
What does the 0 in MATCH mean?
The 0 argument tells MATCH to find an exact match. It is appropriate for matching a specific person name and a specific day header.
How do I copy the lookup formula without changing the table ranges?
Use absolute references for the source ranges, such as $B$8:$F$9, $A$8:$A$9, and $B$7:$F$7. Then drag the fill handle to copy the formula.
