The INDEX MATCH function is a powerful tool in Excel that allows you to perform more advanced lookups. Let’s break it down step by step:
MS WORD ME ENGLISH SE HINDI TYPING KEISE KAREN| MA WORD ME HINDI TYPING KI SETTING kaise Kare
7 amazing Excel tricks and tips| Learn Ms Excel
IUPAC Nomenclature of organic compounds explained in Telegu part 1 NEET/JEE
INDEX Function:
The INDEX function retrieves the value at a given location in a range of cells based on numeric position.
For example, if you have a table of planets and want to get the name of the 4th planet (Mars), you can use the following formula:
= INDEX(B3:B11, 4)
This returns the value in the 4th row of the specified range.
To retrieve the diameter of Mars, you can provide both a row number and a column number:
= INDEX(B3:D11, 4, 2)
This retrieves the value at row 4, column 2.
MATCH Function:
The MATCH function is designed to find the position of an item in a range.
For instance, to get the position of the word “peach” in a list of fruits, you can use:
= MATCH("peach", B3:B9, 0)
This returns 3 since “Peach” is the 3rd item.
MATCH is not case-sensitive and works for both horizontal and vertical ranges.
INDEX MATCH Together:
By combining INDEX and MATCH, you can perform more flexible lookups.
For example, you can look up and return the value of a cell in a table based on both vertical and horizontal criteria.
This dynamic duo is often used as an alternative to the VLOOKUP or HLOOKUP functions.
Remember, mastering INDEX MATCH can significantly enhance your Excel skills!