Need help with Index Small formula

practice1.xlsx


Hi,

Thanks for your guidenace in advance! I have attaced my excel for review. I want a working formula to generate the "expected result" in the excel file.

I got this: =IFERROR(INDEX($A$2:$A$4, SMALL(IF(ISNUMBER($B$2:$D$4), ROW($B$2:$D$4)-ROW($B$2)+1), ROW()-ROW($A$1))), "")

from chatgpt, but it didn't produce the answers in the order I want. I would like to have it from top to bottom in first 2nd column, then from top to bottom again in 3rd column and so on , thx

Are you still using Excel 2016 or something newer?

Your profile needs updating, please. Do this NOW. Thanks.

Should cell D5 be 1 or 12?

Will the numbers in the table section be unique (appearing once only)?

thx, updated my excel version.


Basically, I want the formula to check from B2 to B5, then C2 to C5, then D2 to D5 if it is a number, if it is number then output the row label. If it is blank then skip it to the next cell and check. thx

That didn't answer EITHER of my questions! I understand what you are trying to do.

I asked:

1. Is D5 meant to be 1 or is that a typo? Should it be 12? If not, explain why melon appears at the bottom of the list.
2. In columns B to D, is the data realistic? Will it be numbers and will each of the numbers be unique (i.e. not repeated)?

Please answer the questions.

In the absence of a response to either question, I am assuming that 1 in D5 is a typo and that the numbers will be unique. If so, you can try this:

=IFERROR(INDEX($A$2:$A$5,SUMPRODUCT(($B$2:$D$5=AGGREGATE(15,6,($B$2:$D$5)/($B$2:$D$5<>""),ROWS($A$1:A1)))*(ROW($A$2:$A$5)-1))),"")

And on the assumption that the numbers are NOT unique, use this: (just enter... not an array formula):

=IFERROR(INDEX(A:A,10^6*MOD(AGGREGATE(15,6,(ROW($B$2:$D$5)/10^6+COLUMN($B$2:$D$5))/($B$2:$D$5<>""),ROWS(L$2:L2)),1)),"")

See file. Adjust the bit in red to equal the FIRST cell in which the formula is used. In the file it's in L2....

Another approach, same result, please try and copy down

Formula:

Please Login or Register  to view this content.

Note: Thanks to Trevor and wildknife, from which I learned an important part of this trick yesterday.

Thanks for your feedback and rep . Glad to have helped.

Which formula works? There were several!

Thanks for the rep.

i will need time and chatgpt to understand it :D

Why not just ask Glenn about the bits you don't understand?