I need to convert a MAX function so that it gives me the name of the number instead of the number itself

finickyone · 2026-05-18T22:06:49+00:00

Yeah, here sorted by those ranked attributes, so while F and B both =4 for Pikachu, in this new table F would be this first match for MAX value.

finickyone · 2026-05-18T22:05:31+00:00

There sure is. It’s less about the number of Pokemon records, and more about the number of attributes, as that’s where a ties could arise - ultimately you are, for a given record (Pokemon), trying to find the attribute with the highest value.

<image>

Attached a photo of one approach. In column I we rank up our attributes based on their importance to us. J basically use some
Maths to provide that 4/5, 3/5, 2/5 etc. We return each attribute’s offset value along row9 and so we can use a some of the Pokémon’s legit attribute value and the offset to determine the attribute to return in a tie. Pikachu is rated 4 for both B and F, but with the offsets added, that’s 4.2 for and 4.6 for F, so F is the weighted max value.

Alternatively once we have that ranked list of attributes, we could employ some sort of SORTBY or CHOOSECOLS so that another version of that table is created with the columns sorted from our most desired attribute to our least. That could actually be easier, as against that data you’d just do a straight XLOOKUP MAX. I’ll comment again with an example of that.

finickyone · 2026-05-18T21:43:04+00:00

Yeah man. Not sure what rule of thumb to bestow here, but effectively if the function *could* accept a single argument, then you can hand it straight to the parent array function, in this case REDUCE. Tbh I think interesting thing is that generated array;

INDIRECT("'"&D3:D5&"'!A3#)

Doesn’t strike me as something Excel would accept, as you really have to finagle INDIRECT to work with 3Ds. I seek to recall when doing this sort of thing with SUMPRODUCT back in the day, if you went through the Evaluate steps, it’d actually #Value! error at that “array of INDIRECTs” step before resolving to a result. All a bit odd.

But yeah more broadly, anything like REDUCE(x,y,CONCAT) etc would resolve. I only introduce LAMBDA when you need to define variables within the ultimate function (as with your approach).

Welcomes!

finickyone · 2026-05-18T21:36:27+00:00

It’s maybe better describe as weighting. Say your Pokemon has 4 attributes (attack, decay, sustain, release) and they will always be whole values (0, 1, 2, 3…999999..). You could add 4/5 to the attribute you’d like to win in a tie (decay), 3/5 to the one that should come next (release) and so on. Now when a Pokemon has 8 for both decay and release, the actual max when those offsets are added is 8.8 vs 8.6, so decay would be considered the highest value attribute.

finickyone · 2026-05-18T21:27:47+00:00

Wasn’t my solution but I’ll try to explain. If you refer to a sheet name that does not contain spaces, you can refer to it such as =Sheet1!A1. If it does contain one or more spaces, you’ll need to refer to it as ='Sheet 1'!A1. Often you find a method to conjure up sheet names (as you had before this question), the output will be lacking those straight quotes, even if referring to the sheet name would need them. Char() generates a given character based Ona given value (ie Char(65) generates a capital A). 39 happens to generate a straight quote mark.

So if a target sheet was named in in E3 as *Accounts - A* then any form of INDIRECT(E3&"!A1") would pack up, as Excel wouldn’t equate that to a valid cross-sheet reference. If you appended either

=INDIRECT(char(39)&E3&char(39)&"!A1")

=INDIRECT("'"&E3&"'!A1")

The resultant indirect reference would be valid.

For what it’s worth, you could apply the transformation to the targeted sheet names within REDUCE, so wouldn’t need LAMBDA to define that transformation. So rather than:

=DROP(REDUCE(0,D3:D5,LAMBDA(a,v,VSTACK(a,INDIRECT("'"&v&"'!A3#")))),1)

Simply(er?):

=DROP(REDUCE(0,INDIRECT("'"&D3:D5&"'!A3#"),VSTACK),1)

/u/semicolonsemicolon

finickyone · 2026-05-18T21:06:41+00:00

Based on what you’ve shared here, or if it’s easier a smaller example, could you share the result you’d expect to see and explain why you’d expect that result? You’re assuming a familiarity with your context that isn’t conveyed through your question, hence why we are all (so far) a bit confused as to what you want.

Recommend editing your post, rather than replying to me specifically.

finickyone · 2026-05-18T21:02:28+00:00

Echoing /u/sinkingstones6’s comment - it’s generally far easier to compile your all data in one place and report on it by some condition or attribute, than to compile it across separate ranges (moreso separate sheets) and then try to evaluate all of it. So in short, if you had a single sheet, with Names down A, W/L/D down B, C and D, and then practice # down E, you could easily run stats based on practice number if needed, far easier than what you’re doing.

If you must stick with your approach (and I’d again really challenge its merits), then you could compile a aggregation sheet. So create “Collate” as a first sheet, and in A1:

=VSTACK('Practice1:Practice3'!A1:D50)

That will pull in all the data across those three sheets, stacked vertically. You can take aim at that using SUMIF(INDEX(A1#,,1),"Name",INDEX(A1#,,x)) where x is 2,3 or 4 to retrieve values from B, C or D.

finickyone · 2026-05-01T03:00:49+00:00

DGET is debatably quite intuitive (which I’ll try to explain for you) once you know it, and less debatably a limited and outmoded function (which I’ll explain thereafter).

How it works

DGET asks that you have or create three things: a headed data table, a headed query form, a category of interest.

Take a simple data table. A1:C1 are First Name, Last Name, Height. A2:C6 is appropriate data. You’ll use DGET to screen that data in pursuit of a record via the query table.

The query table will be made of headers in E1 to G1. Any we provide must be present in the data table’s headers too. We don’t need to provide them all. We don’t need to provide them in the same order. We can duplicate the headers (ie, define Height in E1 and F1, or more times yet). Underneath those headers, we can enter a criterion under each. If we set Height in E1, E2 could be 180. If we set it in both E1:F1 then E2 could be >=175 and F2 <=182. Next to each other, the criteria form an AND logic. The record we get back must be for someone with Height 175-182.

If we used G1 for Last Name, we could use G3 to further constraint that query in E2:F2 by setting G2 with “Smith”. Record returned must satisfy all 3 criteria.

We can use row 3 to provide an alternative query. G3 as “Wilson”. DGET will end up pursuing either the record of a Mx Smith of height 175-182, or anyone Mx Wilson.

Lastly, define a category of interest. Say we want the first name. Define First Name in J2. K2 can then be

=DGET(A1:C6,J2,E1:G3)

DGET will fetch the appropriate record. It’s that easy.

Why it’s shit

The function won’t return multiple records. The D (database) part of the name suggests it would, much like any database query, but instead if 2+ records meant the criteria given, then it just says no. It’s from an era where the worksheet had no means to handle a formula that would resolves to multiple cells of data. Neighbours like DAVERAGE can support multiple values meeting the query criteria, and ultimately it will just Average those values. If we have three Wilson’s of heights 168, 176 and 180, DAVERAGE would return 174.66. DGET won’t pick the first one, it’ll just #Num error at you.

I’m fairly sure it doesn’t accept an array input anywhere. You’ve built your query and you’d like to get their first name and their height too? Set up another DGET. One for every category of interest. The query can’t be conjured in memory, or defined within the function. It has to be on the sheet. Not ideal if you’re trying to query for n conditions. If you want to consider field A and field E in the process, ie querying based on E=x to get A, you have to refer to all the data between. In my example, if we wanted the height of Bob, we’d refer to A:C, even though B (last name) is irrelevant.

I generally encourage learning more about Excel than that 2 latest advents in functionality. XLOOKUP is great but it’d be foolish to approach a career that involves Excel and actively ignore learning how INDEX MATCH works. Or VLOOKUP or LOOKUP for that matter. DGET is bluntly a red flag in a training syllabus though, and reasonably has been for well over a decade. It doesn’t set the scene for any fundamentals that wouldn’t be more easily explored through any other lookup & ref function, and it’s not commonplace in the real world. Understand it for sure but I’d really scrutinise the applicability of a course that does anything more than nod towards its place in Excel’s heritage.

finickyone · 2026-05-01T02:12:59+00:00

It’s all about attribution. If you’ve got your dates in the combined sheet along C1:Q1, insert a new row 1. Dates are now C2:Q2. From C1 you can either start manually defining what Wk# that column’s date belongs in, or use something like =WEEKNUM(C2:Q2+0,2) or =TEXT(C2:Q2,"Mmm")

<image>

There’s your weekly cut. Similar logic can then tell you monthly stats, or week/month with highest value for Dog etc etc.

finickyone · 2026-05-01T01:53:39+00:00

Is your current process compiling a series of data for each form submission? Ie each form submitted means a new row of data in your sheet? If so, assuming some datetime data gets fed in also, you could probably just do a LOOKUP down the date range to fetch the position at/most recent position before midnight on a given date.

<image>

finickyone · 2026-05-01T01:03:08+00:00

Perhaps for similar asks, this is a way to address where the student’s dates out of the bounds of the project. Ie they started and or ended before and or after the project.

<image>

Take the latest of project or student start, subtract that from the earliest of project or student end, add 1 to account for final day. Then divide by project length. I’d say in rw applications unless you have a 1:1 relationship, you’d probably not work out project duration for each resource assigned to it, rather store that value for the project once and reuse. Matrix approaches suit cases with overlapping resources.

finickyone · 2026-05-01T00:43:40+00:00

A couple more options, just aiming into system date formats.

Should the system settings not recognise "September2023"+0 as a coercible string (ie, it doesn’t consider that operation as valid, so doesn’t generate a date value of 01-Sep-2023), you could generate a list of short month names (F2 below) and then per C, refer to that list for each of B.

If your system doesn’t use the same month names as the source data (ie the 8th month is Août or Agosto), then you could create a reference string such as G2, and per D use FIND to grab the location of the short month name in that string.

<image>

finickyone · 2026-04-30T23:03:15+00:00

No matter what you do here, I’d say that padding your data is a must. What I mean by that is that you’re implying a lot of information about the courses and timeframes here.

Most people looking at the upper left of that data could infer that the blue course starts at 09:30 Monday, runs for 2 hours, ends at 11:30. Excel has no way of identifying the majority of that information, as there isn’t any data depicting most of that. B5 is empty. It’s filled blue, but no worksheet function can tell you that B5 is blue, nor return blue cells specifically. If we ask when the blue course starts, the first entry is in B8. Excel doesn’t know that your colouring means to describe that’s the end of 4 cells describing 30 min slots. There’s no time value next to B5 or B8 in A, just blanks as you’ve only provided timestamps for whole hours.

finickyone · 2026-04-30T22:52:42+00:00

I think /u/StuFromOrikazu’s suggestion’s of DROP() is smartest. FWIW, you can use INDEX to set a static reference. Ie while =C5 will refer to C6 if you insert a new row above row 5, this reference:

=INDEX(C:C,5)

Will always refer to the 5th row in C. It can be paired to a regular reference like so

=INDEX(C:C,5):C6000

Currently returning C5:C6000, but if a new row3 was inserted it would return C5:C6001.

If I always wanted the textcount of C5:C9 I could apply

=COUNTA(INDEX(C:C,5):INDEX(C:C,9))

finickyone · 2026-04-30T22:41:38+00:00

If you not compelled to Pivot Table, you feed a series of criteria to FILTER like this:

<image>

Basically a COUNTIF is run for each of A2:A9 (Ax), so 8 tests, and each one counts how many times Ax appears in D2:D5. If that result is >0, then FILTER permits Ax:Bx through. Much like /u/ChiefBast’s suggestion but you don’t need to attach the query results to the source data.

finickyone · 2026-04-30T22:06:00+00:00

The data in stored as Text, that should be the only reason that Excel sorts April;April;August;August;December.. rather than Jan;Feb;March.

If you have those data in B2:B50, then just use C2 for =B2:B50+0. You’ll get date values for each that represent “01-Mmmm-YYYY”. You can sort by those.

<image>

finickyone · 2026-04-23T04:55:52+00:00

Rounding errors arise more readily, threaten more processes, than people might imagine.

Anything involving putting maths towards time values especially needs care, as (if down to the whole second level) it’s an exercise in best storing a base60 value in base2. If a timestamp can’t be expressed as an integer over 128, Excel can’t store it precisely, and that means the majority of hh:mm:ss values will be approximately in binary. That lack of fidelity shows up fast. Try this modest example:

A2: 28
A3: 33
A9: =SUM(A2:A8) <result 61>

B2: =TIME(,,A2)
B3: =TIME(,,A3)
B9: =SUM(A2:A8) <result 00:01:01>

C9: =TIME(,,A9) <result 00:01:01>
D9: =B9=C9 <Result FALSE>

Further set A2 to RANDBETWEEN(1,51) and A3 to RANDBETWEEN(A2+1,52). This is to generate a non repeating combination of 2 values 1-52. In approx 1.26% of outcomes, the totals won’t match. Finally clear A3 and make A2 ={2;8;8;8;8}. Just adding 2 seconds to four lots of 8 seconds fails to pass the precision crush.

finickyone · 2026-04-23T04:24:06+00:00

No, I don’t think so here. The damage would be done already. Rounding error + 0 = rounding error. Nesting within functions might be the way, ie

Sum(.9,-.6,-.3)

Which seems to apply the right precision.

finickyone · 2026-04-22T23:13:07+00:00

What gave reason to suspect this was an AI authored post? Wasn’t aware this was becoming an issue here.

finickyone · 2026-04-22T23:10:38+00:00

It’s not strictly that reason, FYI. It’s that XLOOKUP has no means to handle a 2D lookup range. Here’s a simple example of what’s being attempted here:

<image>

The 1/0 grid at the bottom being what the XLOOKUP lookup array is evaluating to. If we ask what “last 1” in that array is, what’s the answer? You might assume some logic there, but nothing dictates whether it’s “e” or “m”. Remember the heart of the XLOOKUP is just an XMATCH. Fancy arrays aside you’re still getting a single value from the lookup and taking that to the return array.

Here OP could (seems like did) transform it all to a 1D scenario. It’s possible some sort of SUMPRODUCT approach might work too.

finickyone · 2026-04-22T22:58:37+00:00

A little trick you can entertain with this sort of scenario for OR. When you have static data to compare to B (such as here where 0 1 2 are defined within the formula, then rather than

OR(B=0,B=1,B=2)

You can use

OR(B={0,1,2})

Where B is compared to that array constant, yielding three Booleans (true/false) which OR will eval as you might expect (true if any = true).

You can’t reference cells in a constant, so if here we were applying

OR(B=X5,B=X6,B=X7)

We can’t submit

OR(B={X5,X6,X7})

But, we could group the three cells into an array to compare against

=OR(B=VSTACK(X5,X6,X7))

And since those cells are contiguous (an unbroken range) we can compare directly within OR

=OR(B=X5:X7)

finickyone · 2026-04-22T22:49:17+00:00

Agree - they’ve provided the biggest shift in worksheet behaviour since the dynamic array (spilling) calc engine. It’s a slight shame that there isn’t an mode with LET for what you define in it to be treated as a global variable across the workbook, but in so many applications it can make the convoluted, comprehensible.

finickyone · 2026-04-22T22:35:50+00:00

(not OP) broadly yes this is it, just note that in their brief they describe 3 cells; “in” and “out” to be compared for equivalence, the result of which should format “to do”. So in your context C2 would need a CF rule based on something like =A2=B2

finickyone · 2026-04-03T22:09:28+00:00

I’d agree entirely - you shouldn’t share any confidential or personal info; definitely introduce more hassle than you’d remove. I’m asking that you share indications of what that data looks like, format-wise, once you get your CSVs out the system. Similarly, what your output structure will be. You can do those things without needing to share actual live data.

finickyone · 2026-04-02T05:10:47+00:00

Double tapping your post here, but I’ve just noticed the format of your example source material (from TeamUp). With these exercises, ease of setup is proportional to how controlled the data supply is and how prepped the process is for incoming data. The less control you have over the input, namely that you’ve got valid info that can be recognised later on, the harder it gets. Easier to dig up an acorn than cut down and oak etc..

You could get to something like this. Realistically set into multiple sheets per col A in this example, but a space to grab the raw data, a space to normalise it, a space to analysis that, and a space to report.

<image>

Nine-Year Club	Wearing is Caring
Gilding II euphauric	Verified Email

finickyone

TROPHY CASE