Excel formula

Janette Coles 20 Reputation points
2026-08-21T10:39:40.4733333+00:00

I am trying to do a formula on a spreadsheet and need help.

Tab 1 - This has column C and Column D

Tab 2 - This has the same information but in Column A and Column B

In Tab 2 each line in Column C has a unique text title.

I want Tab 1 to pick up the unique text title from Tab 2

I've tried various xlookup and vlookup but keep getting errors.

Can anybody advise how to do this?

Many thanks

Microsoft 365 and Office | Excel | For business | Other
Answer accepted by question author
Kristen Tran 1,520 Reputation points Independent Advisor
2026-08-21T11:04:34.9333333+00:00

Good day,

Based on the example provided, you need to perform a lookup using two matching criteria. Excel must compare:

  • Tab 1, Column C with Tab 2, Column A
  • Tab 1, Column D with Tab 2, Column B

When both values match on the same row, Excel can return the corresponding value from Tab 2, Column C into Tab 1, Column M.

Assuming your worksheets are named Tab 1 and Tab 2, you can enter the following formula in M2 on Tab 1:

=XLOOKUP(1,('Tab 2'!$A$2:$A$1000=C2)*('Tab 2'!$B$2:$B$1000=D2),'Tab 2'!$C$2:$C$1000,"Not found")

Then copy the formula down through the rest of Column M.

This approach works because each comparison returns a TRUE/FALSE result, and only rows where both conditions are met evaluate to 1. XLOOKUP then returns the corresponding value from Column C on Tab 2.

For your sample data, the expected results would be:

  • 84459939 + SIP Trunks (See SIP Service) > Voiceflex
  • 01604621731 + Analogue Line > Gamma

Note: I noticed that one of the IDs contains a leading zero (01604621731) in Tab 2, while the corresponding value in Tab 1 appears as 1604621731. If one value is stored as text and the other as a number, Excel may not recognize them as a match.

In that case, you can use the following formula, which converts numeric-looking values before comparing them and helps account for leading zeros:

=XLOOKUP(1,(IFERROR(--'Tab 2'!$A$2:$A$1000,'Tab 2'!$A$2:$A$1000)=IFERROR(--C2,C2))*('Tab 2'!$B$2:$B$1000=D2),'Tab 2'!$C$2:$C$1000,"Not found")

As an additional tip, avoid using entire-column references (such as A:A) in multi-condition lookup formulas, as this can affect performance. Also, ensure that all referenced ranges are the same size and start/end on the same rows. If your regional settings use semicolons (;) instead of commas (,), replace the separators accordingly.

I hope this points you in the right direction. If you continue to experience issues, please let me know the exact error message or the version of Excel you're using, and I'll be happy to assist further.

Thank you for your understanding, and wishing you a wonderful day ahead.  

If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Was this answer helpful?

2 people found this answer helpful.

1 additional answer

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-08-21T10:39:56.77+00:00

    If the value in Tab 1 column C matches the value in Tab 2 column A, and the value to return is the unique text title in Tab 2 column C, use a lookup formula that searches one column and returns from another.

    Use this in Tab 1 on the row where the result should appear:

    =XLOOKUP(C2,Tab2!A:A,Tab2!C:C)

    If the matching value is in Tab 1 column D instead, use:

    =XLOOKUP(D2,Tab2!A:A,Tab2!C:C)

    If XLOOKUP is not available, use VLOOKUP only when the lookup column is the left-most column in the lookup range. For example:

    =VLOOKUP(C2,Tab2!A:C,3,FALSE)

    This works because:

    • C2 is the value to find.
    • Tab2!A:C is the lookup range.
    • 3 returns the value from column C of that range.
    • FALSE forces an exact match.

    If errors such as #N/A appear, common causes are:

    • the value in Tab 1 does not exactly exist in Tab 2
    • extra spaces or inconsistent text formatting
    • the wrong lookup column is being used

    If a #SPILL! error appears, avoid using a full-column lookup value such as A:A as the first argument. Use a single cell like C2, then fill the formula down.

    Example:

    • In Tab 1 E2 enter =XLOOKUP(C2,Tab2!A:A,Tab2!C:C)
    • Press Enter
    • Copy the formula down

    If the actual match is:

    • Tab 1 column C to Tab 2 column A, use C2
    • Tab 1 column D to Tab 2 column B, use D2 and adjust the lookup range accordingly

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.