Forum Discussion

Ashwin540's avatar
Ashwin540
Icon for Microsoft rankMicrosoft
Dec 22, 2022
Solved

Excel value match

Hi  I'm trying to achieve an lookup match where 2 sheets included Scenario: Sheet 1 having the names and ID column(need to fill )sheet 2 got name and ID  The sheet 1 have A and B where with ref...
excel
  • Harun24HR's avatar
    Harun24HR
    Dec 22, 2022

    Ashwin540 Use XLOOKUP().

    =XLOOKUP(A2:A4,Sheet2!A:A,Sheet2!B:B)
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 22, 2022

Ashwin540 

Since you are using Excel 365 (or 2021), Harun24HR 's version is the appropriate formula.

= XLOOKUP(A2:A7, Sheet2!A2:A4, Sheet2!B2:B4,"ID Not Found")

The formula goes in cell B2 and it will spill to populate the column.

I would also recommend reading up about Excel Tables and Structured references.  They offer a better approach to referencing source data than entire column references.

 

Resources