Forum Discussion
Solved
Need simplified version
Hi all, I tried to work this out myself, but it was very labor intensive and not very tidy. For some reason, the formulas wouldn't spill! i have a list of names, that appear in several arrays on on...
Can you check whether the following formula in cell B3 brings the desired result? If not, I may have misunderstood something.
=INDEX(XLOOKUP(A3:A22,'Competition results'!K3:K100,'Competition results'!O3:O100),ROW(1:20))+INDEX(XLOOKUP(A3:A22,'Competition results'!V1:V98,'Competition results'!Y1:Y98),ROW(1:20))Excel Insider or Excel for the Web can also use the following formula:
=BYROW(HSTACK(XLOOKUP(A3:A22,'Competition results'!K3:K100,'Competition results'!O3:O100),XLOOKUP(A3:A22,'Competition results'!V1:V98,'Competition results'!Y1:Y98)),LAMBDA(in,SUM(in)))
