ThisCalculator

Everyday

Lookup Second Value: Formula & Examples

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This calculator finds the second matching value in a list or table when a lookup key appears more than once. You enter the lookup key, the lookup range, and the return range, and it returns the value from the second match (or a clear message if fewer than two matches exist). The formula uses array logic to count matches and pick the second one, which standard VLOOKUP cannot do.

Formula

The core formula is an array formula that works in Excel and Google Sheets:

=INDEX(return_range, SMALL(IF(lookup_range=lookup_key, ROW(lookup_range)-MIN(ROW(lookup_range))+1), 2))

Enter it with Ctrl+Shift+Enter in classic Excel (in Excel 365 and Google Sheets, just Enter works).

  • lookup_range – the column or row where you search for the key (e.g., A2:A10).
  • lookup_key – the value you want to find (e.g., “B”).
  • return_range – the column or row from which you want the result (e.g., B2:B10). It should be the same size as lookup_range.
  • ROW(lookup_range)-MIN(ROW(lookup_range))+1 – converts each cell’s row number to a relative position (1, 2, 3, …).
  • IF(…) – creates an array of those positions only where the key matches; elsewhere it returns FALSE.
  • SMALL(…, 2) – picks the second smallest position, i.e., the second match.
  • INDEX(return_range, position) – returns the value at that position in the return range.

If there are fewer than two matches, the formula returns #NUM! or #VALUE! depending on the spreadsheet. You can wrap it in IFERROR to show a friendly message.

Worked Example

Using the default values from the calculator: lookup key = “B”, lookup range = A2:A10, return range = B2:B10.

Suppose the data is:

  • A2: A, B2: 10
  • A3: B, B3: 20
  • A4: C, B4: 30
  • A5: B, B5: 40
  • A6: A, B6: 50
  • A7: B, B7: 60
  • A8: C, B8: 70
  • A9: A, B9: 80
  • A10: B, B10: 90

Step 1: Identify rows where A = “B”. Those are rows 3, 5, 7, and 10 (relative positions 2, 4, 6, 9).

Step 2: SMALL(…, 2) gives the second smallest position: 4.

Step 3: INDEX(B2:B10, 4) returns the value in the 4th cell of B2:B10, which is B5 = 40.

So the second match for “B” is 40. The calculator shows 40.

Why VLOOKUP Can’t Do This

VLOOKUP (and XLOOKUP by default) returns only the first match. If your data has duplicate keys, VLOOKUP will always give the first one, no matter how many times the key appears. That’s a common frustration when dealing with orders, transactions, or repeated entries. The array formula above overcomes that by using SMALL to jump to the nth match. You can change the last argument (2) to 3, 4, etc., to get the third, fourth, or any match.

How to Use the Calculator

Enter your lookup key (the value you want to find), then paste or type your lookup range and return range as cell references (e.g., A2:A100). The calculator assumes your ranges are in the same sheet and have the same number of rows. It returns the second match’s value. If you need the second match from a different column, just change the return range.

Common Mistakes and Fixes

  • Not entering as an array formula – In older Excel, you must press Ctrl+Shift+Enter. If you just press Enter, you may get a #VALUE! error or the first match.
  • Mismatched range sizes – If lookup_range and return_range have different numbers of cells, INDEX will return a #REF! error. Always make them the same size.
  • Using whole columns (A:A) with ROW() – That works, but it can slow down the sheet. Limit to your actual data range.
  • Case sensitivity – The formula is case-insensitive by default in Excel. If you need case-sensitive matching, use EXACT inside the IF.
  • No second match – The formula returns #NUM!. Use IFERROR to display a custom message like “Only one match found”.

Alternatives for Getting the Second Match

Besides the array formula, there are other ways to get the second match:

  • Helper column – Add a column with =COUNTIF($A$2:A2, A2) to number each occurrence, then use VLOOKUP with the key and occurrence number.
  • FILTER function (Excel 365 / Google Sheets) – Use =INDEX(FILTER(return_range, lookup_range=key), 2) to get the second value directly.
  • SMALL with ROW() – The array formula is the classic method that works in all Excel versions.

Frequently Asked Questions

What if I need the third or fourth match?

Change the number 2 in SMALL(…, 2) to 3, 4, etc. For example, SMALL(…, 3) gives the third match. If you need a variable n, you can replace the 2 with a cell reference that contains n.

Does this work in Google Sheets?

Yes. In Google Sheets, you don’t need Ctrl+Shift+Enter; just type the formula normally. The logic is identical.

Why does my formula return #VALUE! instead of the second match?

This usually happens when you don’t enter it as an array formula in older Excel. Select the cell, press F2, then press Ctrl+Shift+Enter. If you’re in Excel 365, it should work automatically. Also check that your ranges don’t contain errors.

Can I use this to find the second match in a different sheet?

Yes, just include the sheet name in the range reference, like Sheet2!A2:A10. The ROW() function still works because it references the row numbers in that sheet.

What if the return range is not the same size as the lookup range?

INDEX will return #REF!. Make sure both ranges have the same number of rows (or columns if horizontal). The formula assumes a one-to-one correspondence.

Is there a way to get the second match without an array formula?

Yes, use a helper column with COUNTIF to number occurrences, then use VLOOKUP with the key and occurrence number. That’s often easier for beginners.

Related

Formula used
in key | Lookup key | - | B | 0
in look | Lookup range | - | A2:A10 | 0
in ret | Return range | - | B2:B10 | 0
out second | Second match value | - | INDEX(ret, SMALL(IF(look=key, ROW(look)-MIN(ROW(look))+1), 2)) | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.