Forum Discussion
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 one spreadsheet. these names have results in the same row that i need.
I want to collect these results and tally them next to the same name on a new spreadsheet.
On top of that the names need to ordered according the results tally from highest to lowest.
i have done all this except for the ordering from highest to lowest in the screenshots below
i actually only need to show the info in columns A and B.
Hope i have explained myself clearly enough.
Cheers Richard.
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)))
7 Replies
- Attach a sample workbook. You may need SORT() function.
- Hello Tinny426
OK why not use the SUMIF() function?
Look it up and see if it will work for you. It is backward compatible to MS-Excel ??? Gosh how old is that???
Let us know.
Georgie Anne- Hello GeorgieAnne,
I have used the SUMIF() function along with my other functions and it works, but its long!
SUM(XLOOKUP(A3,'Competition results'!E3:E100,'Competition results'!I3:I100),XLOOKUP(A3,'Competition results'!U3:U100,'Competition results'!X3:X100)).
And its only going to get longer when i add more XLOOKUP() FUNCTIONS.
I was hoping for just the one function that i can SUM().
Cheers Richard.
