"Day 9: Excel Essentials Unveiled - Sharing Today's Insights on My Learning Adventure! 📊🚀 #ExcelSkills #LearningJourney"

#datascience #anlyst #excel #data

EXCEL - 8

LOOKUP AND REFERENCE FUNCTIONS. :-

Function :-

Row - It will return the row index as per the cell reference.
Rows - It returns the no.of rows in a ( range ).
Column - It returns the column index of the reference column.
Columns :- it will count and return the column number.

QUICK RECAP :-

Filter - It takes input as a range and a multiple condition and gives output as a table.
Important :- It doesn't take wildcards.

=FILTER(range, include, [if_empty])

It takes input as range and condition → condition should return boolean output so that it can give output a result.

Isnumber :- It will find out that anything in a number is numeric or not.

=ISBLANK(cell)

Filter function
Takes different arguments in Google sheets and Excel.

Multiple condition :-

=filter(B2:B16,(B2:B16=B2)+(C2:C16=C3))
(*) AND function
(+) OR function

Sort :-

It gives output in a dynamic format. It sorts the data in ascending and descending order.
Sortby :- It sorts it but takes different parameters.
=SORT(range, [sort_index], [sort_order], [by_col])

Unique :- Give us unique values within a column.

LOOKUP FORMULA

HLOOKUP :- Help us to find in a horizontal format.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Match :- it helps us to get the exact position of the cell.
=MATCH(lookup_value, lookup_array, [match_type])

Xmatch :- It is more flexible than the match function.
=XMATCH(lookup_value, lookup_array, [if_not_found], [match_mode], [search_mode])

Index :- It helps us to get the value at a particular position.
=INDEX(range, row_num, col_num)

VLOOKUP :- Helps us to get them in a vertical format. Right to left look up.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

XLOOKUP :- Both horizontal & vertical
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Index-match ( formula mix up is better than lookup)
=INDEX(return_range, MATCH(lookup_value, lookup_range1, 0), MATCH(lookup_value2, lookup_range2, 0))

Wild card is necessary to try to learn and use it. = ‘ * ’

Follow me on this where every day will be added if i learn something new about it :- https://dev.to/nitinbhatt46

Thank you for your time.