# ROWS Function

Celia Muller 2 weeks ago

# Set up a Lookup Table with All Trucks and Matching Plates in 2 Separate Columns . Use: . ✧ INDEX and MATCH ✧ VLOOKUP ✧ XLOOKUP

. ✧ INDEX and MATCH . ✧ VLOOKUP . ✧ XLOOKUP (not working with older Excel versions) https://exceljet.net/articles/index-and-match https://exceljet.net/functions/vlookup-function https://exceljet.net/functions/xlookup-function

1 Like

Lacey Jenkins 2 weeks ago

Công thức công khai cho các bạn đây (một số người cứ lợi dụng người khác gặp vấn đề là inbox gạ... hay trục lợi gì đó) =INDEX(\$A\$1:\$C\$3,MOD(ROW(1:1)-1,ROWS(\$A\$1:\$C\$3))+1,INT((ROW(1:1)-1)/COLUMNS(\$A\$1:\$C\$3))+1)

6 Likes

Arne Schoen 2 weeks ago

I2=IFERROR(SMALL(D:D,1+SUMPRODUCT(ISNUMBER(MATCH(\$D\$2:\$D\$20,\$I\$1:I1,))*1)),"") J2=IF(OR(\$I2="",COUNTIF(\$D:\$D,\$I2)<COLUMN(A1)),"",OFFSET(\$E\$1,MATCH(\$I2,\$D\$2:\$D\$300,)+COLUMN(A1)-1,))

2 Likes

Candida Greenholt II 3 weeks ago

Yes, strings that contain only numbers and exceed 15 significant digits will cause a problem due to the fact that the sumif(s) and countif(s) functions will apply Excel's 15 digit character limit, where the numbers after the 15th digit are converted to 0. And, these functions convert the strings to numbers internally, so it does not matter that you have them formatted as text (if the text could not be converted to a number, then these functions work fine). See screenshot for example - the last 3 digits are different, but countif is treating them as the same.

I think this is one alternative to doing what you're asking: =IFERROR(INDEX(Table1[ASNnro], SMALL(IF(MATCH(Table1[ASNnro], Table1[ASNnro], 0)=ROW(INDIRECT("1:"&ROWS(Table1[ASNnro]))), MATCH(Table1[ASNnro], Table1[ASNnro], 0)), ROW(INDIRECT("1:"&ROWS(Table1[ASNnro]))))), "")

1 Like

Westley Kerluke 1 month ago

Hi, check this out, using QUERY and RANK (which is actualy not necessary, as the names wlll always be ranked in order 1 to 9). =QUERY(B4:C14,"SELECT B, SUM(C) WHERE C IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC",0).

Hope this helps !

1 Like

Lela Barton 1 month ago

2 Likes

Rachelle Pfeffer II 1 month ago

If you have Office 365, try.. =LET(n,A10:A15,x,D2:O7,y,D10:O15,yf,TAKE(y,,1),yc,DROP(y,,1),tbl,TRANSPOSE(WRAPROWS(TOCOL(HSTACK(IFNA(n,yc),IFNA(yf,yc),CHOOSEROWS(DROP(x,,1),XMATCH(yf,TAKE(x,,1))),yc),,1),ROWS(n)COLUMNS(yc))),SORT(FILTER(tbl,(INDEX(tbl,,3)<>0)(INDEX(tbl,,4)<>0))))

u can also create a user-defined function? (i don't know what to call it) using lambda if u'll use the formula several times on ur worksheet.. create a defined name for below formula and name your function, i name it "MatchUnpivotArray"

=LAMBDA(Invoices,Array1,Array2,LET(n,Invoices,x,Array1,y,Array2,yf,TAKE(y,,1),yc,DROP(y,,1), tbl,TRANSPOSE(WRAPROWS(TOCOL(HSTACK(IFNA(n,yc),IFNA(yf,yc),CHOOSEROWS(DROP(x,,1),XMATCH(yf,TAKE(x,,1))),yc),,1),ROWS(n)COLUMNS(yc))), SORT(FILTER(tbl,(INDEX(tbl,,3)<>0)(INDEX(tbl,,4)<>0)))))

1 Like

Toni Torphy 1 month ago

First, you should remove the function iferror, so that we can see the real function outcome.

Second, i guess you input "row(\$G\$49:\$G\$68) which will return the number from 49 to 68, means only 20 rows, so i think this should be the cause of your problem.

Hope this helps.

1 Like

Alvena Schinner 1 month ago

you mean GKXLOOKUP??? Xlookup works like all the other lookup function, it returns the FIRST (or LAST if doing a bottom up search) it is NOT THE FILTER function to return a whole array of values

ALSO YOU NEED TO SHOW THE FORMULA you are using otherwise there is no way to help at all

2 Likes

Selena Kunde 2 months ago

Minerva Rempel if you are using MS365, then can transform the data using one formula

• Formula used in cell A2

=LET(X,I3:I26,VSTACK(HSTACK({"Worker-ID","Name"},TRANSPOSE(UNIQUE(L3:L26))),IFERROR(UNIQUE(HSTACK(I3:I26,J3:J26,MAKEARRAY(ROWS(X),MAX(COUNTIF(I3:I26,I3:I26)),LAMBDA(r,c,INDEX(FILTER(K3:K26,X=INDEX(X,r)),c))))),"")))

1 Like

Candida Greenholt II 2 months ago

Assuming you have office 365 (or a dynamic array version of excel that has these functions), I would first separate the names and values into separate columns (may be able to use text to columns feature).

Then, say the data is in A1:B7, you could try: =LET(names, UNIQUE(A1:A7), values, BYROW(TRANSPOSE(IF(A1:A7=TRANSPOSE(names), B1:B7, "")), LAMBDA(rw, TEXTJOIN(" ", TRUE, SORT(UNIQUE(rw, TRUE))))), names&" "&values)

Or, you might check out power query and see if this is something it could do.

3 Likes

Amely Lindgren 3 months ago

Công thức mảng (nhập bằng Ctrl + Shift + Enter):

H2=IFERROR(INDEX(B:B,SMALL(IF(B\$2:B\$15<>B\$3:B\$16,ROW(B\$2:B\$15)),ROWS(H\$2:H2))),"")

I2=IF(H2="","",SUM(C\$2:C\$15*ROW(B\$2:B\$15)>=SMALL(IF(B\$2:B\$15<>B\$1:B\$14,ROW(B\$2:B\$15)),ROWS(I\$2:I2)))*ROW(B\$2:B\$15)<=SMALL(IF(B\$2:B\$15<>B\$3:B\$16,ROW(B\$2:B\$15)),ROWS(I\$2:I2)))))

1 Like

Karine Nolan 4 months ago

Sinh viên giờ học level cao quá. Cái này ko thể giải quyết bằng công thức excel trực diện, hàm random luôn nhảy như nhảy dây, nếu nó trùng số bạn phải thay đổi số khác. Bạn hãy kết hợp vba và match để lấy đc số như mong muốn nha

1 Like

Selena Kunde 4 months ago

Sienna Wisozk to make things easier use defined names for your table and use this

=LET( _product, A3:A7, _age, B2:F2, MAKEARRAY( ROWS(_product), COLUMNS(_age), LAMBDA(r, c, SUMIFS( QTY, PRODUCT, INDEX( _product, r,

``````            ),
Age,
">=" &
TEXTBEFORE(
INDEX(
_age,
,
c
),
"-",
,
,
,
29
) + 0,
Age,
"<=" &
TEXTAFTER(
INDEX(
_age,
,
c
),
"-",
,
,
,
100
) + 0
)
)
)
``````

)

0 Likes