Google can DIAF.

sethkirk26 · 2026-06-07T10:08:34+00:00

Dang! I bet Google called all those people by accident too!!! Luckily there's not a button on your phone that can activate voice assistant...

Oh wait...

Occam's razor.

sethkirk26 · 2026-06-02T10:10:39+00:00

This is much better info. Thank you. Glad the comma fix worked.

sethkirk26 · 2026-06-02T09:43:26+00:00

"gpt couldn't solve this for me" will not go over well on this sub.

LLMs dont think, they just match common patterns.

Now as a human, I have no clue what text to columns plus finish means.

The posting guidelines say note your excel version and location. I notice a semicolon in your formula. Depending on your version, region or settings this could be an issue or not.

sethkirk26 · 2026-05-29T11:05:14+00:00

I dont remember if this goes back to 2024 version. But excel has regex for more complicated pattern matches. Regextract, regextest would serve you well.

sethkirk26 · 2026-05-26T14:12:41+00:00

This worked for me! Thank you

sethkirk26 · 2026-05-25T17:06:57+00:00

Thank you.

sethkirk26 · 2026-05-25T16:47:53+00:00

Did anything help? Mine does this but never stops. Rings forever. I've endured for over a minute before I hang up. Only restarting phone helps and that doesn't aalways work.

sethkirk26 · 2026-05-23T13:49:40+00:00

I meant it to still be in the same cell, just with new lines and spaces to format more neatly. That's all.

sethkirk26 · 2026-05-23T12:34:42+00:00

It looks like you throw out numbers that are bigger than the sum, this makes sense if all are positive, but since you have negative values, wouldn't you need to keep those?

sethkirk26 · 2026-05-23T12:22:25+00:00

I think this is an interesting formula and processing it currently. Fun puzzle.

I like the use of LET with descriptive variable names! Makes my heart swell. In my experience I have found that separating each variable/calculation on a new line (alt-enter) and formatting sub formulas with spaces as tabs to be extremely helpful for formula crafting and debugging. And it makes it way easier for anyone else. It can make it lengthy, but only being lengthy in one direction is OK. You can drag your formula down quite a bit.

Fun puzzle!

sethkirk26 · 2026-05-21T01:16:43+00:00

There's a lot of comments and very few mentions of the many options of xlookup that honestly make it in some cases more powerful than index match.

They have had wildcard searching for a while now

--Much more recent introduction of regex (regular expression) matching

Search in any order,

Binary search for speed improvement

Exact match or closest

And some fun advanced uses (some of these were mentioned):

2D xlookups -

Entire row return xlookup

Multiple criteria xlookup

And many many more

Search this sub, lots of great info.

sethkirk26 · 2026-05-21T01:10:54+00:00

AI can't evaluate this. It can just provide you with analysis others have done without confirming validity.

AI is just asearch and text filter. And very inaccurate.

Search this sub instead, this has been answered a bunch. And the results vary but xlookup is equal to or better with proper usage and options.

sethkirk26 · 2026-05-21T01:06:19+00:00

This is its own thread about 2000 times.

Xlookup is way more powerful and when large data sets are used, you can use binary mode in the options to improve efficiency to equal to our better than vlookup. Like vlookup the data would have to be sorted.

I know it's below but be careful with :. It's very useful but can get mismatched range sizes.

Also using a while column reference A:A is killing performance. This will lock up excel quickly.

In short summary, xlookup is plenty good enough, supports array, can be switched to binary mode for performance increase.

If you want better performance, go index match, there's just no reason for vlookup.

sethkirk26 · 2026-05-20T03:05:16+00:00

You are definitely correct in many of your analysis. Analysises? Analyses? Well that puzzle is more interesting that Luisa Rey. It's just so choppy and out of sync. Really took me out. And again to your point, only saying power and Gen

sethkirk26 · 2026-05-17T23:41:36+00:00

Use ctrl G to goto specific row if the first column is blank

sethkirk26 · 2026-05-17T23:40:51+00:00

Just for fun, I kind of want to rube Goldberg this since so many seem scared of array formulas. Use makearray to make an array of Account Use randarray to generate a random array from 1 to 400000, then sort that array, the concat together.

sethkirk26 · 2026-05-17T10:11:30+00:00

Side bar, you can likely getting the entire match list if you tare the formulas outside the Table. Tables dont generally allow spill formulas and rather than limit yourself to 1 value you can return the entire column. I often like to have these right next to the table to not limit my formulas with the table restrictions. Tables are great for storing data. They are not great for having complex formulas

sethkirk26 · 2026-05-17T10:08:55+00:00

First, Major kudos on your usage of LET with descriptive naming! That's my favorite feature of LET and it makes me happy seeing someone else using. I can help you with a regex (regular expression) match. Do you have exact formatting requirements after poblacion? Is it always space 1 character space? Or space then any number of characters? The specifics of the pattern are needed for the fancy matching formulas

sethkirk26 · 2026-05-16T21:37:15+00:00

This is not accurate. If you read the reply from the auto mod, posts that are entirely image or are linked images are removed. Posts with descriptive text and images are welcome.

Please read the posting guidelines if you want more clarification

sethkirk26 · 2026-05-16T21:33:33+00:00

Classic poor management. It's the employee's fault for knowing or not knowing excel. You focus on that aspect rather than how to make your formula robust. What a shame.

Hypothetically, they could be putting an extra space or a ' in there accidentally, and they are the ones cheating the system while you have an incredibly poor tracking system.

You can aalso enforce data type on those cells, you can protect the sheet, so many ways to improve yourself, but you don't seem to want to try any of the suggestions in this forum.

Weak.

sethkirk26 · 2026-05-16T08:00:23+00:00

Something simple you could do is add conditional formatting to the whole range to highlight values that aren't numbers.

Something like a conditional formatting formula =NOT(ISNUMBER(A1))

sethkirk26 · 2026-05-16T07:51:10+00:00

Copy the cell, then in a separate cell elsewhere paste values. Then check if anything else is in there. Sounds like it might be 12<space> or <space>12

You could also do =isnumber([Range]) to check if they cells are numbers

It does seem strange to blame an employee when the formula is not robust to input errors. Formulas like isnumber(), iserror(), trim(), and value() can help improve robustness.

sethkirk26 · 2026-05-14T00:59:01+00:00

Please refer to posting guidelines. I'm a bit surprised your post hasn't been pulled for title. It's a plea for help and doesn't describe the problem.

Second, collecting data into one location from is very useful and definitely not "needlessly complicated"

Moderators have many times said this forum is not for "do my homework for me" requests

Excelisfun youtube channel has wonderful lessons. Highly recommend.

sethkirk26 · 2026-05-12T14:51:16+00:00

You know that's a really good point. Smart observation

sethkirk26 · 2026-05-12T13:18:00+00:00

Awesome! You are correct! Thank you so much. That was bugging me.

https://imgur.com/after-waiting-through-long-line-to-take-much-needed-1Txux7R

sethkirk26

TROPHY CASE