Forum Discussion
Find value from one array in another array and return another cell
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.