We all are familiar with the basic syntax of IF. There are many instances when one needs to create flags based on some constraints. Note: One can combine INDEX and MATCH to overcome the limitation of VLOOKUP. You can also use +1 and -1 based on your requirements. The 3 rd argument is used for the exact match. This is because the number 8 is at the third position. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. Being a lookup and reference function, it works for both an exact and approximate match. The output is the first position found for the given value. The MATCH function MATCH Function The MATCH function looks for a specific value and returns its relative position in a given range of cells. In the below example, we are trying to find “Rajesh Ved” in the Employee Name column. This Excel advanced formula returns the row or column number when there is a specific string or number in the given range.
Note: If you insert another row into the 5 th row, the formula will return “Chandan Kale.” Hence, the output would depend on any changes happening in the data table over time. The above formula would return “Rajesh Ved.” When using both row and column number, the syntax would look like something like this: The same INDEX formula can be used in getting value along the row. We can use the advanced excel formula as below:
To get the name of an employee at the 5 th observation, Below is the data. This advanced excel formula is used to get the value of a cell in a given table by specifying the number of rows, columns, or both.
You can download this Advanced Excel Formulas Template here – Advanced Excel Formulas Template #1 – VLOOKUP Formula in Excel