Forum Discussion
Find value from one array in another array and return another cell
List one has many names in column A with a sales total in column B
Dave - $200
Tim - $300
John - $150
List two just has names in Column A and no sales (Not all of the name are in both list)
Dave
John
Sue
Alex
If list two contains a name from list one then I want to put return the sales in list two column B.
Any idea what function I need?
I have office 365
Thanks,
Dave
5 Replies
dave, as PeterBartholomew1 suggested that could be XLOOKUP on most modern Excel or more traditional VLOOKUP or INDEX/MATCH. The question is are your lists in different worksheets in one workbook or they are in separate workbooks?
SergeiBaklan Thank you both for your help. I will have to look at the XLOOKUP, I am not familiar with it yet. I am using office 365 at work and I assume that it is the latest version but I am not 100% sure that it has the update that was mentioned.
I have the raw data with names and sales totals in Sheet 1 and my matching list is in Sheet 2. The reason the names in the list are different is that some employees are no longer with the company and there are some new ones.
For Example:
Sheet 1
A B
Dave $400
Steve $250
Tim $456
Sara $600
John $325
Sheet 2
Dave
Alex
Tim
John
--------------------------
The solution in sheet 2 should look like this:
A B
Dave $400
Alex
Tim $456
John $325
Thoughts?
Thanks again for your help.
My guess is that a corporate license is most likely on semi-annual update and you would need IT intervention if you were to be set up to evaluate dynamic array solutions. SergeiBaklan will be able to advise further on this.
Without dynamic arrays, I personally would use a Table for the report as well as the source data so that your formulas are readable and propagate to fill the entire field (note: this is not the traditional way of working and has only been available since 2007)
= IFERROR( VLOOKUP( [@Name], Source, 2, FALSE ), "" )
or
= IFERROR( INDEX( Source[Amount],
MATCH( [@Name], Source[Name], 0 ), "" )
If you need solutions that use direct referencing, rather than tables, I am sure others will be able to help.
= 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.
