Extend an Excel Formula to Include a Third Lookup Value
Question details
The user wants to add a third value to a horizontal sequence without hard-coding each value in the formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Generate a horizontal sequence such as Tags, Test Team, and Test Type from a lookup source.
- Observed behavior
- The desired sequence should return each item in source order and accommodate additional items with minimal formula changes.
Choose an empty area for the source list and place the values in the order you want them returned. Use unique, nonblank values so MATCH can identify each position.
Use an Excel table to return the next lookup value
Use a table and an INDEX and MATCH formula to extend the sequence without adding nested IF conditions.
Enter Sequence in H1, then enter Tags, Test Team, and Test Type in H2:H4, one value per cell.
Select H1:H4, choose Insert > Table, and confirm that the table has headers. On the Table Design tab, set the table name to SeqTable.
In A2, enter =INDEX(SeqTable[Sequence],1) to return the first value from the table.
In B2, enter =IFERROR(INDEX(SeqTable[Sequence],MATCH(A2,SeqTable[Sequence],0)+1),"") and drag the fill handle to the right. Each cell looks up the value to its left and returns the next table entry.
Enter another value directly below the table so the table expands, then drag the formula farther right if needed. The structured table reference includes the new row.

Build the Same Lookup Sequence in WPS Spreadsheet
Use a source range with INDEX and MATCH in WPS Spreadsheet to generate a horizontal sequence without a separate IF condition for each value.
- 1. Enter the source values: Open WPS Spreadsheet and enter Tags, Test Team, and Test Type in H2:H4.
- 2. Start the sequence: In A2, enter =INDEX($H$2:$H$4,1) to return the first source value.
- 3. Look up the next item: In B2, enter =IFERROR(INDEX($H$2:$H$4,MATCH(A2,$H$2:$H$4,0)+1),"").
- 4. Fill the formula across: Drag the fill handle from B2 to the right to return the remaining values. Cells after the last item display a blank.
- 5. Include additional values: Add a new item below H4, update both source-range references in the B2 formula to include the new row, and fill the updated formula across again.

Frequently Asked Questions
How do I add a fourth value to the Excel sequence?
Enter the value directly below SeqTable so the table expands, then drag the horizontal formula farther right. The structured reference automatically includes the new table row.
Why does the formula return a blank after Test Type?
Test Type is the last source item, so there is no next row to return. IFERROR displays a blank when INDEX goes beyond the end of the table.
Can the source list contain duplicate values?
Avoid duplicates with this formula. MATCH finds the first occurrence of a value, so a repeated item may return the wrong next value.
Can I use different sequences in the same workbook?
Yes. Create a separate source table for each sequence and point the INDEX and MATCH references to the appropriate table, or use a selector cell with a formula designed to choose the source.




