Home Course Advance Excel Ethical Hacking Anonphisher TBomb Virus-Builder Seeker Linux Basic Tools Download Links CiLocks Fun Commond Cyber Security Advance Excel CMD Commond Excel Shortcut Excel Formating Shortcut Keys MS-Office Free Activation Commond Windows Free Activation Commond Windows Shortcut keys

Advanced Excel Formulas

1. VLOOKUP

Searches vertically for a value in the first column.

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

2. HLOOKUP

Searches horizontally in the top row.

=HLOOKUP(lookup_value, table_array, row_index_num, FALSE)

3. INDEX + MATCH

Powerful combo for flexible lookups.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

4. XLOOKUP

Modern lookup both vertically and horizontally.

=XLOOKUP(lookup_value, lookup_array, return_array, "Not found")

5. IF + AND/OR

Use logical tests with multiple conditions.

=IF(AND(A1>10, B1<5), "Yes", "No")

6. SUMIFS

Sum values with multiple criteria.

=SUMIFS(sum_range, criteria_range1, criteria1)

7. COUNTIFS

Count items with multiple conditions.

=COUNTIFS(range1, condition1, range2, condition2)

8. TEXTJOIN

Join values with a delimiter, skipping blanks.

=TEXTJOIN(", ", TRUE, A1:A5)

9. FILTER

Dynamic filtering based on conditions (Excel 365).

=FILTER(A2:A10, B2:B10="East")

10. UNIQUE

Extracts unique values from a range.

=UNIQUE(A2:A100)

11. SORT

Sorts a range or array.

=SORT(A2:B10, 1, TRUE)

12. INDIRECT

Returns a reference from a text string.

=INDIRECT("A" & B1)