How to Find Second Match in Excel Using XLOOKUP [4 Methods]

ByRhidi Barma Updated on Excel Formulas

Follow these steps to find the second match in Excel using XLOOKUP:

  1. Choose cell E3.
  2. Type the formula: =XLOOKUP(E1&E2, A2:A10&SORTBY(SEQUENCE(ROWS(A2:A10),1,2)-MATCH(SORT(A2:A10),SORT(A2:A10),0),SORTBY(SEQUENCE(ROWS(A2:A10),1,2),A2:A10,1),1),B2:B10)
  3. Hit ENTER to see the result.

This formula looks for the second occurrence of “Maurene Gillio” by combining the contents of cells E1 and E2 in a list (A2:A10). To make this search efficient, it sorts the list in a special way. When it finds the position of the combined value in the sorted list, it retrieves the matching value “248” from the range B2:B10 using the XLOOKUP function.

1. Find the Second Match Using the XLOOKUP Function in Excel

XLOOKUP is a powerful function in Microsoft Excel that allows you to search for a specific value in a column and retrieve a corresponding value from another column.

In this case, I want to find the second match of the “Maurene Gillio” from the “Name” column and retrieve the corresponding  2nd match value from the “Score” column.

Syntax

=XLOOKUP(lookup_value&nth, lookup_array&nth_lookup_array, return_array)

Formula

=XLOOKUP(E1&E2, A2:A10& SORTBY(SEQUENCE(ROWS(A2:A10),1,2)-MATCH(SORT(A2:A10),SORT(A2:A10),0), SORTBY(SEQUENCE(ROWS(A2:A10),1,2),A2:A10,1),1),B2:B10)

Formula Breakdown

  • This Excel formula employs the XLOOKUP function to search for a value created by concatenating the contents of cells E1 and E2 within a specific range (A2:A10). 
  • The formula uses advanced sorting techniques, including custom sorting orders generated by the SORTBY and SEQUENCE functions, to optimize the lookup process.
  • The MATCH function is utilized to identify the position of the search value within a sorted array (A2:A10 after sorting). It determines the relative position of the concatenated value in the sorted list.
  • Once the match position is found, XLOOKUP retrieves the corresponding value from the range B2:B10, providing a powerful way to perform a customized, sorted lookup based on the concatenated values of E1 and E2.

Use the XLOOKUP function to find the second match in Excel. Follow the steps below:

  1. Select cell E3.
  2. Copy this formula: =XLOOKUP(E1&E2, A2:A10& SORTBY(SEQUENCE(ROWS(A2:A10),1,2)-MATCH(SORT(A2:A10),SORT(A2:A10),0), SORTBY(SEQUENCE(ROWS(A2:A10),1,2),A2:A10,1),1),B2:B10)
  3. Press ENTER.Using XLOOKUP function to find second match

Final Result

The second occurrence of “Maurene Gillio” has been located, and it corresponds to the value “248.”

XLOOKUP function final output to find second match

You can also use this function to find the “n-th” match. For example, if you need to locate the third occurrence, simply input “3” into cell “E2,” and the function will automatically identify the third match.

XLOOKUP function final output to find nth match

2. Lookup Vertically to Get the Second Match Using FILTER Function in Excel

The FILTER function in Excel is a useful tool for extracting data according to predetermined standards. For different data analysis jobs, finding the second match using FILTER helps identify the second instance of a condition inside a dataset.

Syntax

=INDEX(FILTER(array,include),row_number)

Formula

=INDEX(FILTER(B2:B10,A2:A10=E1),E2)

Formula Breakdown

  • This Excel formula first filters the values in column B (B2 to B10) based on a condition in column A (A2 to A10) where it matches the value in cell E1. The FILTER function extracts values from B2:B10 that correspond to the condition specified in A2:A10.
  • It then uses the INDEX function to retrieve the value at the location indicated in cell E2 from the filtered list. Essentially, it fetches the value in the filtered list corresponding to the row specified by E2.

Utilize the FILTER function to vertically lookup the second match in Excel. The process is outlined below:

  1. Select cell E3.
  2. Use this formula: =INDEX(FILTER(B2:B10,A2:A10=E1),E2)
  3. Press ENTER to see the result.Using FILTER function to find second match

Final Result

This formula combines INDEX and FILTER functions to extract specific values, like ‘Maurene Gillio,’ and find their second occurrence in a list.

FILTER function final output to find second match

3. Locate the Second Match in Excel Using the INDEX and AGGREGATE Function

When working with a dataset in Excel, the INDEX and AGGREGATE functions can be used to quickly locate and obtain the second instance of a certain value or condition.

Syntax

=INDEX(array,AGGREGATE(function_number,options,(ROW(ref_1)/(ref_2),[k]))

Formula

=INDEX(B2:B10,AGGREGATE(15,6,(ROW(A2:A10)-1/(A2:A10=E1),E2))

Formula Breakdown

  • This formula combines the INDEX and AGGREGATE functions to perform a data lookup in Excel. It extracts data from the range B2:B10. The AGGREGATE function works by identifying the row number of a specific value in column A (A2:A10) that matches the value in cell E1.
  • It does this by calculating the position based on the ROW function, adjusted for matches with the expression (1/(A2:A10=E1)). The ROW function generates an array of row numbers for the range A2:A10, and the expression (1/(A2:A10=E1)) produces an array of 1s and errors, with 1s in positions where A2:A10 matches the value in cell E1.
  • The AGGREGATE function then uses its arguments (15 and 6) to ignore errors, sort the resulting array, and return the ‘k-th’ smallest value, which, in this case, is the row number where the first match occurs.
  • The result from AGGREGATE, which represents the row number, is then used with INDEX to retrieve the corresponding value from the range B2:B10. Essentially, it fetches the data from B2:B10 that corresponds to the row number of the first occurrence of the value in cell E1 in the range A2:A10.

Use a combination of INDEX and AGGREGATE functions to vertically look up the second match in Excel. Here’s how:

  1. Choose cell E3.
  2. Write the formula: =INDEX(B2:B10,AGGREGATE(15,6,(ROW(A2:A10)-1/(A2:A10=E1),E2))
  3. Press ENTER.Using AGGREGATE function to find second match

Final Result

The INDEX and AGGREGATE functions work together to locate and extract the value “248” associated with the second instance of the condition “Maurene Gillio”.

AGGREGATE function final output to find second match

4. Using IF and SMALL Functions to Lookup the Second Match in Excel

Here, I have employed the IF and SMALL functions to locate the second match efficiently.

Syntax

=INDEX(array, SMALL(IF(logical_test, reference, k))

Formula

=INDEX(B2:B10, SMALL(IF(A2:A10=E1, ROW(A2:A10)-ROW(A2)+1), E2))

Formula Breakdown

  • This formula combines the INDEX and SMALL functions to extract a value from the range B2:B10. It begins by using the IF function to check if values in the range A2:A10 match the content of cell E1 and, if they do, it generates an array of corresponding row numbers.
  • The ROW function assists in creating this array of row numbers. Subsequently, the SMALL function retrieves the value from cell E2, which represents the position of the desired value within the B2:B10 range. Finally, the INDEX function employs this row number to fetch the corresponding value.

Utilize a combination of IF and SMALL functions to vertically look up the second match in Excel. Follow the steps below:

  1. Select cell E3.
  2. Type the formula: =INDEX(B2:B10, SMALL(IF(A2:A10=E1, ROW(A2:A10)-ROW(A2)+1), E2))
  3. To insert the formula press ENTER.Using IF & SMALL function to find second match

Final Result

After applying this formula, we find the expected result.

IF & SMALL function final output to find second match

Conclusion

In this article, I’ve discussed four methods to discover the second match in Excel. Typically, the XLOOKUP function is employed to search for a value within a range and retrieve a corresponding value from another range. Moreover, XLOOKUP can also be utilized to find the “n-th” match in Excel.

Frequently Asked Questions

How do I find my second instance in XLOOKUP?

To find the second instance in XLOOKUP in Excel, use the SMALL function within an array formula. Here’s a sample formula:

=XLOOKUP(lookup_value, lookup_array, return_array, , , SMALL(IF(lookup_array=lookup_value, ROW(lookup_array)-MIN(ROW(lookup_array))+1), 2))
  1. Press Ctrl+Shift+Enter to apply the formula.
  2. Adjust ‘lookup_value’, ‘lookup_array’, and ‘return_array’ as needed.

What if XLOOKUP has multiple matches?

If XLOOKUP in Excel has multiple matches, it returns the first match it encounters. For all matches, consider using INDEX and MATCH or array formulas.

Can you do a double XLOOKUP?

Excel’s XLOOKUP doesn’t support a direct ‘double XLOOKUP.’ To achieve a similar result, you can use other methods like INDEX and MATCH.

  1. Use the formula: =INDEX(return_array, MATCH(lookup_value1 & lookup_value2, criteria_array1 & criteria_array2, 0))
  2. Concatenate the criteria (lookup_value1 and lookup_value2).
  3. Compare the concatenated criteria with the concatenated criteria in the data (criteria_array1 and criteria_array2).
  4. MATCH finds the position of the match.
  5. INDEX retrieves the corresponding result from return_array.

How do I select a second value in VLOOKUP?

In a standard VLOOKUP function in Excel, you cannot directly select a second value. VLOOKUP is designed to return the first match it finds based on a specified lookup value. If you need to retrieve a second value, you may consider using alternative methods.

If you want to select a second value in Excel, consider using the INDEX and MATCH functions for a more versatile lookup process. Example: =INDEX(B:B, MATCH(D1, A:A, 0) + 1)

5/5 - (2 votes)

Data Analyst

Howdy! I'm Rhidi Barma. I'm a professional Excel user. I've completed my graduation from Jahangirnagar University, Bangladesh. I've been working with Microsoft Excel since 2015. I love writing blogs on MS Excel tips & tricks, data analysis, business intelligence, capital market, etc. I love to spend my leisure time reading books and watching movies. To know more about me, you can follow me on social media platforms. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *