Advance Excel Formulas

 Excel - Advance Formulas:

VLOOKUP
=VLOOKUP(B5,marksheet!$A$3:$O$13,8,FALSE)
HLOOKUP
=HLOOKUP(C21,$B$16:$E$18,2,FALSE)

VLOOKUP/MATCH
=VLOOKUP($A2,Sheet1!$A$2:$Y$9995,MATCH('Vlookup Match'!B$1,Sheet1!$A$1:$Y$1,0),FALSE)

REVERSE LOOKUP
=VLOOKUP(A2,CHOOSE({1,2},Sheet1!$N$1:$N$9995,Sheet1!$G$1:$G$9995),2,FALSE)

IS/VLOOKUP

=IF(ISTEXT(C18),VLOOKUP(C18,B2:C15,2,FALSE),VLOOKUP(C18,A2:C15,3,
FALSE)

IFERROR/VLOOKUP

Discount Calculation
=IFERROR(VLOOKUP(S2,$AD$17:$AG$19,MATCH(H2,$AD$16:$AG$16,0),TRUE),"No Discount")
Discount with Amt.
=IF(ISERROR(S2*T2),S2,ABS(S2*T2-S2))

Status-1
=IF(VLOOKUP(N2,$N$1:$U$9995,8,FALSE)>10000,"Very Good","Work Hard")
Status-2
=IF(ISNA(VLOOKUP(K2,$AA$10:$AA$14,1,FALSE)),"No Record Found","Yes We Have Record")
Status-3
=IF(ISNA(VLOOKUP(K2,$AA$10:$AB$14,2,FALSE)),"No Info",VLOOKUP(K2,$AA$10:$AB$14,2,FALSE))
Status-4
=IF(VLOOKUP(N2,$N$1:$U$9995,8,FALSE)>10000,VLOOKUP(N2,$N$1:$U$9995,8,FALSE)*5%,VLOOKUP(N2,$N$1:$U$9995,8,FALSE)*0%)

INDEX/MATCH
=INDEX(Sheet1!$A$1:$Y$9995,MATCH(index!$A2,Sheet1!$N$1:$N$9995,0),MATCH(index!B$1,Sheet1!$A$1:$Y$1,0))

TIPS AND TRICKS
1. Copy the numbers in down series press Ctrl+D from keyboard

2. Format Cell for value first select cell then press Ctrl+1
Then click on Custom ==> Type Coding:
[<40] POOR;[<80] "GOOD";"VERY GOOD"

3. Insert Photo in Comment Box, First insert comment for cell or Emp_ID
then right click on outline of comment box==>Format Comment==>Color &
Lines==>Fill Effects==>Picture then select any photo