Forum Discussion
Find value from one array in another array and return another cell
= XLOOKUP( Name, Source[Name], Source[Amount], "" )
This assumes you have a modern dynamic array version of Excel which is gradually being rolled out to Office 365 users. At a more specific level it assumes the source data is held in an Excel Table (so that it resizes as data is added and provides structured references). The other table needs to be a simple range but with defined Names to identify the data.
The XLOOKUP function is intended to replace virtually all existing lookup strategies. Since 'Name' is a multicell range the function will return the entire column of results as a dynamic range. XLOOKUP defaults to an exact match and the 4th parameter is the value to return when the lookup value (name) is not found. It may be as simple as a blank (as here) or could be an entirely separate calculation such as a lookup to an alternative table.