Excel value match



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 reference with sheet 2 i need ID value to be filled in sheet 1 column B(ID)


Please help me on below




6 Replies
best response confirmed by Ashwin540 (Microsoft)

@Ashwin540 Use XLOOKUP().



Hi Ashwin,


As Harun24HR has mentioned in his reply, XLookup is the best solution to your requirement.


Another way to work on your problem is you can use VLookup function along with IFError function in your "ID" column of Sheet1 as below

=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"ID Not Found")


For your reference, please find the attached workbook with the above formula.



Hi thank you for the reply, but the same logic when i try for different data iget below error 




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.


Hi , Glad for the response but i am getting an SPILL error when i try with multiple columns data in sheet 1 and same 2 rows in sheet 2


I could not replicate your error; then a further possibility occurred to me.  If you are using 365, but have only recently moved to it, you may be using a formula such as

= XLOOKUP(homeName,Table1[Name],Table1[ValueA],"ID Not Found")

that generates the entire column of results corresponding to the array 'homeName', but then filled the formula down so that copies of the result array overlap one another. 

[Note1: earlier versions of Excel would only have returned a single result from the array to each formula cell] 

[Note2: I have converted the entire column references to Structured References by using an Excel Table, an approach introduced in Office 2007]

1 best response

Accepted Solutions
best response confirmed by Ashwin540 (Microsoft)

@Ashwin540 Use XLOOKUP().


View solution in original post