XMATCH
XMATCH is the modern version of MATCH.
Syntax
=XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])
Example:
=XMATCH(“Rahul”,B2:B5)
Returns the position of Rahul.
LOOKUP
Purpose
Performs a lookup and is commonly used with sorted data for approximate matching.
Syntax
=LOOKUP(lookup_value,lookup_vector,[result_vector])
Example:
|
Marks |
Grade |
|
0 |
F |
|
40 |
C |
|
60 |
B |
|
75 |
A |
|
90 |
A+ |
Formula:
=LOOKUP(82,A2:A6,B2:B6)
Result:
A
CHOOSE
Purpose
Returns a value based on its position in a list.
Syntax
=CHOOSE(index_num,value1,value2,…)
Example:
=CHOOSE(3,”Sales”,”HR”,”IT”,”Finance”)
Result:
IT
Practice
Create a department selector using CHOOSE and an employee position lookup using XMATCH.