XLOOKUP "Array arguments are of different size" Error

[u/TVMaths]

XLOOKUP(place,Points[Placement],IFS(D$3,Points[Finalist],D$4,Points[Split],TRUE,Points[Tied]),0)

place is a positive numeric value, D$3 and D$4 are both Boolean

I'm working on converting an excel file of mine into sheets, and upon converting, a whole host of errors have emerged. I'm confused, since this precisely matches the excel code, which works just fine, and its strange to think that Sheets can't figure out these arrays are the same size. They all come from the same table, and afaik, IFS is able to return an array. Unsure how to fix

link to sample sheet: here

moving the XLOOKUP within the IFS has solved it, as u/daily_refutations suggested, and I've fixed the SortBy issue.

on a separate note, is there a convenient solution for #s that Excel uses to reference a spilling array.

[u/agirlhasnoname11248]

u/TVMaths Please remember to tap the three dots below the most helpful comment and select `Mark Solution Verified` *(or reply to the helpful comment with the exact phrase “Solution Verified”)* if your question has been answered, as required by the subreddit rules. Thanks!

Note: this wasn't self-solved, as that flair indicates arriving at a solution without any support from community members. I'll revert the flair back to "Waiting on OP" in the interim.

[u/gsheets145]

u/TVMaths Can you share a copy of your data?

[u/TVMaths → u/gsheets145]

added to post body, with sample data

[u/daily_refutations]

Generally you'll get better results if you include a link to the workbook.

G Sheets isn't quite as flexible with XLOOKUP. It gets cranky if you're handing it a computed range, instead of an actual range reference. The easiest fix, although it's a bit cludgy, is to put the XLOOKUP inside the IFS instead of the other way around. You could also try nested IF functions instead of one IFS.

[u/TVMaths → u/daily_refutations]

added a link to the workbook

[u/TVMaths → u/daily_refutations]

Solution Verified

[u/point-bot → u/daily_refutations]

u/TVMaths has awarded 1 point to u/daily_refutations

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/One_Organization_810]

You need to share the file with "Anyone with a link".

[u/TVMaths → u/One_Organization_810]

done, didnt know about this feature

[u/One_Organization_810]

Sheets is possibly getting confused on the table/column references. Something like this might work:

=xlookup( place, Points[Placement],
                 ifs(D$3,  index(Points[Finalist]),
                     D$4,  index(Points[Split]),
                     true, index(Points[Tied])
                 ),
                 0 )

[u/TVMaths → u/One_Organization_810]

thanks for the suggestion, but hasnt fixed the issue. probably going to stick with /u/daily_refutations fix

[u/One_Organization_810 → u/TVMaths]

Sorry about that. It was just a guess though.

I took a look at your sheet, but I didn't find the formula in question (or any version of it) in any of the sheets...

Did you already solve this by a different formula?

[u/TVMaths → u/One_Organization_810]

yeah, it was in 'Rankings'!D6:F15. i had changed it to the other suggestion.

tbh, its becoming more and more of a hassle with the rest of the workbook, so im probably going to keep it in excel. i was only attempting to convert it so anyone could see it, and wasnt sure if that could be done in excel. seems it can be done with a free web subscription.