=XLOOKUP(A2, B:B, C:C, "Not found")Find A2 in column B, return value from C=VLOOKUP(A2, B:D, 3, FALSE)Exact match, return 3rd column of B:D=INDEX(C:C, MATCH(A2, B:B, 0))Lookup in any direction=IFERROR(VLOOKUP(...), 0)Show 0 instead of #N/A=SUMIFS(E:E, B:B, "North", C:C, ">100")Sum with several conditions=COUNTIFS(B:B, "North", D:D, "Yes")Count rows meeting all conditions=AVERAGEIFS(E:E, C:C, "Laptop")Average with a condition=SUMPRODUCT((B2:B99="North")*E2:E99)Array-style conditional sum=IF(B2>=40, "Pass", "Fail")Two outcomes=IFS(B2>=90,"A", B2>=75,"B", TRUE,"C")Many outcomes without nesting=AND(B2>0, C2="Yes")All conditions true=OR(B2="Delhi", B2="Noida")Any condition true=TRIM(A2)Remove extra spaces=PROPER(A2)Capitalise Each Word=TEXTJOIN(", ", TRUE, A2:C2)Join cells with a separator=LEFT(A2, 4) / RIGHT / MIDExtract characters=TEXT(A2, "dd-mmm-yyyy")Format a date as text=TODAY() / =NOW()Current date / date-time=EOMONTH(A2, 0)Last day of the month=NETWORKDAYS(A2, B2)Working days between dates=DATEDIF(A2, B2, "m")Months between two dates=UNIQUE(B2:B500)Distinct list=FILTER(A2:E500, C2:C500="North")Rows that meet a condition=SORT(A2:E500, 5, -1)Sort by 5th column, descending=SEQUENCE(12)Numbers 1 to 124 more formula groups, 12 daily shortcuts and a printable PDF. Enter your name and mobile to open it instantly.
No spam. A counsellor may call once to help you pick the right course.