Forum Discussion
Ashwin540
Microsoft
Dec 22, 2022Excel 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...
Ashwin540
Microsoft
Dec 26, 2022Hi , 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
PeterBartholomew1
Dec 26, 2022Silver Contributor
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]