Search results

1] It is the original lookup range: 2] If the row 3 and row 4 exchange the order, and become>> Then, your above formula will give wrong result Regards Bosco

1] Your formula: =INDEX($A$2:$A$7;AGGREGATE(15;6;(ROW($E$2:$E$7)-1)*(SEARCH($E$2:$E$7;E11)^0);1)) 2] Better using "/" instead of "*"+"^0" Formula result giving same and a bit shorter =INDEX($A$2:$A$7;AGGREGATE(15;6;ROW($E$2:$E$7)-1/SEARCH($E$2:$E$7;E11);1)) Regards

Thank you for your great catch.

Another approach, using Find instead of Exact function =IF(A2="","",IFERROR(IF(FIND(A2,UPPER(A2)&LOWER(A2))=1,"Upper","Lower"),"Mix")&" case")

Or you can use this shorter: =FIND(LEFT(A2),"SDT")+IFERROR(FIND("C",A2)-4,0) Herein: SDT means Single, Double and Triple C means Child or Children.

Try, In C2, formula copied down: =FIND(LEFT(A2),"SDT")+IFERROR(SEARCH("ch",A2)-4,0)

Try to change your formula: From this >> =IF.ERROR(@INDEX(T$2:T$400;AGGREGATE(15;6;(ROW REF($T$2:$T$400)-ROW REF($T$2)+1)/($U$2 :$U$400<>"");ROWS(T1:$T$1)));"") Into this >> =IF.ERROR(@INDEX(T$2:T$400;AGGREGATE(15;6;(ROW REF($T$2:$T$400)-ROW...

Sorry, I don't have Office 365.

A long formula for the old Excel version. 1] In A8, formula copied down: =IFERROR(IFERROR(INDEX('Appollo For Natural Oils'!$A$1:$A$1000,AGGREGATE(15,6,ROW($A$1:$A$1000)/(MATCH('Appollo For Natural Oils'!$A$1:$A$1000&'Appollo For Natural Oils'!$D$1:$D$1000,'Appollo For Natural...

And, you can use VLOOKUP function instead of INDEX+MATCH in your example. In cell F5, formula copied down: =IFERROR(E5-VLOOKUP(D5,A:B,2,0),"not applicable") Or, =IFERROR(E5-VLOOKUP(D5,A:B,2,0),"") Regards

Try to set up output table as per below In L2, formula : =SUMPRODUCT((D$2:D$999=J3)*(F$2:F$999=K3)*ISNUMBER(SEARCH("yr",G$2:G$999)),H$2:H$999)

E14: =IFERROR(LOOKUP(9^9,D3:D12/(A3:A12=B15)/(B3:B12=B16)/(C3:C12=B17)),"")

please open a new thread for your new question.

Maybe, In F11 enter formula: =LOOKUP(9^9,H4:H8/(LOOKUP(ROW(B4:B8),ROW(B4:B8)/(B4:B8>0),B4:B8)=C11)/(C4:C8=C12)/(D4:D8=C13))

Or, using SUMPRODUCT function instead of SUM+IF =SUMPRODUCT((((A2:A7="ICNMAN")+(B2:B7="Economy")+(C2:C7="A12345"))>0)*D2:D7)

You need to set up a "Resource Allocation Table" from Sheet1 to sheet6 So, In "Sheet1" the below formulas copied down: [G3] =IFERROR(@FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("+",,C$3:C$164),"+","</b><b>")&"</b></a>","//b["&ROW(A1)&"]"),"") In "Sheet1" the below formula copied across right...

@Hany ali 1] Please be note the forum rule: one post one question, 2] Being for the formula to extract unique names from all Sheets of the file, the revised Excel 2019 formula will be: In B2, formula copied down...

Convert amount to lacs or thousand Maybe something like this: 1] In A2, enter your amount 2] In B2, enter formula: =FILTERXML(WEBSERVICE("https://excelkida.com/xml/amount-to-word?style=ind&lang=en&num="&A2),"/data")

Or, =TRIM(F1&" "&B1&" "&C1&" "&D1)

I enclosed a English version Excel file for your action.

Try, 1] J2, copied down: =SUMIFS(C:C,B:B,">="&H2,B:B,"<="&I2,A:A,F2) 2] K2, copied down: =J2/7 Regards

Try, =TEXT(LEN(A2)-FIND(".",A2&"."),"0;;;")

Try, In D12, formula copied across and down: =SUM(ISNUMBER(OFFSET($C$2,MATCH($C12,$C$3:$C$8,0),6,,25))*(OFFSET($C$2,MATCH($C12,$C$3:$C$8,0)+2,6,,25)=D$11)) Or, =SUM(OFFSET($C$2,MATCH($C12,$C$3:$C$8,0),6,,25)*(OFFSET($C$2,MATCH($C12,$C$3:$C$8,0)+2,6,,25)=D$11)) Remark: please confirm which...

Open a new thread for your new question. This post is closed.

Basically you need 2 Vlookup and Iferror functions for multiple tables lookup & sum, something like: In F2, formula copied down: =SUM(IFERROR(VLOOKUP(A2,A$2:B$15,2,0),0),IFERROR(VLOOKUP(A2,C$2:D$15,2,0),0)) Regards