Forum Discussion
MicrosoftExcel 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 reference with sheet 2 i need ID value to be filled in sheet 1 column B(ID)
Please help me on below
Ashwin540Hi , 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 2I 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]
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.
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.
