Forum Discussion
Find value from one array in another array and return another cell
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.
Yes, usually corporate environment is on Semi-Annual channel. And usually IT has nothing against moving some limited number of people on Monthly channel if rules of the game are defined for them.
However, both have no Dynamic Arrays. So far DA is only for Insiders Fast and on Monthly Targeted channel (aka Insiders Slow). Recently DA was deployed for Excel Online, but Microsoft removed it back, something went wrong. I don't know what are Microsoft plans, but expect general availability of Dynamic Arrays in an year or so, more close to next Christmas.
Using of XLOOKUP without Dynamic Arrays? - Yes, that's a nice toy, but IMHO, only with DA you may find all power of the function.
davidmj54 , without DA, XLOOKUP and tables the function in B1 of Sheet2 could be
=IFNA(VLOOKUP(A1,Sheet1!$A:$B,2,FALSE),"")and drag it down.
