Forum Discussion

JaWa05's avatar
JaWa05
Copper Contributor
Aug 15, 2023
Solved

Matching data across tabs

Hello All

 

My Excel ability is very limited and I'm trying to have a current spreadsheet be more user friendly.

 

I would like Tab 1 to search inside Tab 2 for the same reference number (which would not be in any particular order). The reference number appears in Column A for both tabs, once it finds the reference number, I would then like the date from Tab 2 Column J to appear on Tab 1.

 

I'm sure this has something to do with INDEX and MATCH, but can't for the life of me make it work.

 

Any guidance would be greatly appreciated!

excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Aug 16, 2023

    JaWa05 

    I asked my secret contact at Microsoft:

     

    Sure, I can help with that. You’re correct that you can use the INDEX and MATCH functions in Excel to achieve this. Here’s how you can do it:

    1. Click on the cell in Tab 1 where you want the date from Tab 2 to appear.
    2. Enter the following formula: =INDEX('Tab 2'!J:J, MATCH(A1, 'Tab 2'!A:A, 0))

    Here’s what this formula does:

    • MATCH(A1, 'Tab 2'!A:A, 0) looks for the value in cell A1 of Tab 1 within column A of Tab 2. It returns the relative position of the matching value within column A of Tab 2.
    • INDEX('Tab 2'!J:J, ...) returns the value in column J of Tab 2 at the position returned by the MATCH function.

    Please replace 'Tab 2' with the actual name of your second tab.

    Remember to press Enter after typing the formula. Excel should now display the date from Tab 2 in the selected cell of Tab 1 whenever there’s a matching reference number.

    Let me know if you need further assistance! 😊

     

2 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Aug 16, 2023

    JaWa05 

    I asked my secret contact at Microsoft:

     

    Sure, I can help with that. You’re correct that you can use the INDEX and MATCH functions in Excel to achieve this. Here’s how you can do it:

    1. Click on the cell in Tab 1 where you want the date from Tab 2 to appear.
    2. Enter the following formula: =INDEX('Tab 2'!J:J, MATCH(A1, 'Tab 2'!A:A, 0))

    Here’s what this formula does:

    • MATCH(A1, 'Tab 2'!A:A, 0) looks for the value in cell A1 of Tab 1 within column A of Tab 2. It returns the relative position of the matching value within column A of Tab 2.
    • INDEX('Tab 2'!J:J, ...) returns the value in column J of Tab 2 at the position returned by the MATCH function.

    Please replace 'Tab 2' with the actual name of your second tab.

    Remember to press Enter after typing the formula. Excel should now display the date from Tab 2 in the selected cell of Tab 1 whenever there’s a matching reference number.

    Let me know if you need further assistance! 😊

     

Resources