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)))
Attach a sample workbook. You may need SORT() function.
- Hi Harun, have you had a chance to look at my worksheet yet?
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)))- Thanks for your reply dscheikey,
Your formula works for the job, but i came up with this formula, which works too.
=SUM(XLOOKUP(F6,'Competition results'!$K$3:$K$100,'Competition results'!$O$3:$O$100),XLOOKUP(F6,'Competition results'!$Z$3:$Z$100,'Competition results'!$AC$3:$AC$100).
i have made other improvements as well, so its easier to just stick with the formula i have.