need to help for beginning level student

# [ Excel Advanced Functions and Formulas ] CheatSheet



1. Advanced Lookup Functions

● Index-Match: =INDEX(return_range,MATCH(lookup_value,lookup_range,0))
● Double Index-Match:
=INDEX(return_range,MATCH(row_value,row_lookup,0),MATCH(col_value,c
ol_lookup,0))
● Multi-criteria Index-Match:
=INDEX(return_range,MATCH(1,(criteria1=range1)*(criteria2=range2),0
))
● Offset-Match:
=OFFSET(reference,MATCH(lookup_value,lookup_range,0)-1,0)
● Xlookup basic: =XLOOKUP(lookup_value,lookup_array,return_array)
● Xlookup with not found:
=XLOOKUP(lookup_value,lookup_array,return_array,"Not Found")
● Xlookup exact match:
=XLOOKUP(lookup_value,lookup_array,return_array,,-1)
● Xlookup wildcard:
=XLOOKUP("*"&lookup_value,lookup_array,return_array)
● Xlookup multiple returns:
=XLOOKUP(lookup_value,lookup_array,{return_array1,return_array2})
● Xlookup reverse lookup:
=XLOOKUP(lookup_value,lookup_array,return_array,,,-1)

2. Array Formulas

● Sum array: =SUM(IF(criteria_range=criteria,sum_range))
● Count array: =SUM(IF(criteria_range=criteria,1))
● Average array: =AVERAGE(IF(criteria_range=criteria,average_range))
● Multi-condition array:
=SUM(IF((criteria1_range=criteria1)*(criteria2_range=criteria2),sum
_range))
● Unique values array: =UNIQUE(range)
● Sort array: =SORT(range,[sort_column],[sort_order],by_col)
● Filter array: =FILTER(range,criteria_range=criteria)
● Sequence generation: =SEQUENCE(rows,[columns],[start],[step])
● Array spill: =range#
● Dynamic array expansion: =INDIRECT("A1:A"&COUNTA(A:A))




By: Waleed Mousa

, 3. Text Functions

● Extract first word: =LEFT(A1,FIND(" ",A1&" ")-1)
● Extract last word: =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
● Extract nth word: =TRIM(MID(SUBSTITUTE(A1," ",REPT("
",100)),((n-1)*100)+1,100))
● Case-sensitive find: =FIND(find_text,within_text)
● Case-insensitive find: =SEARCH(find_text,within_text)
● Replace multiple instances:
=SUBSTITUTE(text,old_text,new_text,instance_num)
● Clean text: =CLEAN(TRIM(text))
● Proper case with exceptions: =IF(ISNUMBER(SEARCH("
mc",LOWER(A1))),PROPER(A1),PROPER(SUBSTITUTE(A1," mc"," Mc")))
● Remove special characters: =REGEXREPLACE(A1,"[^a-zA-Z0-9]","")
● Extract numbers from text: =--REGEXREPLACE(A1,"[^0-9.]","")

4. Date and Time Functions

● Last day of month: =EOMONTH(date,0)
● First day of month: =EOMONTH(date,-1)+1
● First day of next month: =EOMONTH(date,0)+1
● Next occurrence of day: =date+MOD(target_day-WEEKDAY(date),7)
● Working days calculation: =NETWORKDAYS(start_date,end_date,holidays)
● Add working days: =WORKDAY(start_date,num_days,holidays)
● Fiscal year start:
=DATE(YEAR(date)+(MONTH(date)>fiscal_month),fiscal_month,1)
● Quarter from date: =ROUNDUP(MONTH(date)/3,0)
● Week number: =WEEKNUM(date,[return_type])
● ISO week number: =ISOWEEKNUM(date)

5. Math and Statistical Functions

● Weighted average: =SUMPRODUCT(range,weights)/SUM(weights)
● Percentile calculation: =PERCENTILE.INC(range,k)
● Mode multiple values: =MODE.MULT(range)
● Median excluding zeros: =MEDIAN(IF(range<>0,range))
● Moving average: =AVERAGE(OFFSET(cell,0,0,n,1))
● Exponential moving average: =α*current+(1-α)*previous
● Standard deviation sample: =STDEV.S(range)

By: Waleed Mousa