Function Problems

Extend an Excel Formula to Include a Third Lookup Value

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

The user wants to add a third value to a horizontal sequence without hard-coding each value in the formula.

Extend an Excel Formula to Include a Third Lookup Value
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the source list

Enter Sequence in H1, then enter Tags, Test Team, and Test Type in H2:H4, one value per cell.

2
Format the list as a table

Select H1:H4, choose Insert > Table, and confirm that the table has headers. On the Table Design tab, set the table name to SeqTable.

3
Start the horizontal sequence

In A2, enter =INDEX(SeqTable[Sequence],1) to return the first value from the table.

4
Return the next value

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.

5
Extend the source when needed

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.

Use an Excel table to return the next lookup value
End of the sequence: The formula returns a blank after the final item. IFERROR also hides other formula errors, so check the source list and table name if an expected value is missing.
Work with lookup formulas in WPS

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. 1. Enter the source values: Open WPS Spreadsheet and enter Tags, Test Team, and Test Type in H2:H4.
  2. 2. Start the sequence: In A2, enter =INDEX($H$2:$H$4,1) to return the first source value.
  3. 3. Look up the next item: In B2, enter =IFERROR(INDEX($H$2:$H$4,MATCH(A2,$H$2:$H$4,0)+1),"").
  4. 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. 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.
Add lookup values to a source list instead of nesting more IF statements.Open and edit Microsoft Excel XLSX and XLS files.Use familiar spreadsheet cells, formulas, and fill handles.
microsoft office alternative - wps office

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.