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

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)

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,))

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]))))), "")

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).

https://docs.google.com/spreadsheets/d/1NYBfHD2nJGi7PMkaNhlp04S6sBgTtMF-INOoXCQ0xoc/copy?usp=sharing

Hope this helps !

could you please write your expected result here .. thanks

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)))))

Creo que ArrayFormula es incompatible con sum, no así con sumif

Giuseppe Parker =COUNTIF(D3:H3,CHAR(252))

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.

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

You could copy and paste the formula here, it is not as clear to read in the image.

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))))),"")))

Minerva Rempel here is the video for you using FILTER() Function

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.

Meredith Howell I refer image below for a solution using INDEX() & ROWS() + COLUMNS()

• Formula used in cell B10

=IFERROR(INDEX($A$1:$A$50,COLUMNS($B$1:B1)+(ROWS(B$1:B1)-1)*3),"")

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)))))

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

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
        )
    )
)

)

Elta Watsica formula in cell k2 will be =SUMPRODUCT(--(K10:V17=TRUE))/(ROWS(K10:V17)*COLUMNS(K10:V17)) and in cell k5 =SUM(--(K$10:K$17=TRUE))/ROWS(K$10:K$17) and fill it right till v5