Advanced excel formulas can be used to lookup values or text in Excel and return the relative cell address.
An INDEX function can be used to MATCH the lookup value in a range of cells.
INDEX function can be used in two ways, INDEX reference and INDEX array
The reference form returns the reference to the cell at the intersection of a particular row and column.
Array form returns more than one value at a time. When using index array press ctrl+shift+enter instead of just enter key.
Syntax
=INDEX(array, row_num,[column_num)
=INDEX(reference, row_num, [column_num],[area_num])
Example 1:
=INDEX((A2:A7),2,3)
A2:A7 is the range of cells to look for the value.
2 is the row number in the range of cells where the value is.
3 is the column number in the range where the value is. There are 3 columns in the table
The INDEX function returns 26 which is the point of intersection between the second row and third column.
When using INDEX array press CRTL+SHIFT+ENTER keys.
Example 2: From the above table use the data to look for text in column A and return the relative absolute cell reference.
Go to cell A10 and type Mary
On cell B10, type the formula =CELL("ADDRESS",INDEX(($A$2:$A$7,MATCH(A10,$A$2:$A$7,0)))
Press enter key
From the above example cell, B10 returns cell reference with the text Mary.
Cell A2:A7 is the column range with the lookup text, A10 is the lookup text.
The formula can only find the first relative cell address which matches the lookup text.
Example 3:
A formula to return the row number of the cell value in the table.
Look for an employee called John and return the cell address.
On cell B10 type the formula =SMALL(IF($A$10=$A$2:$A$8,ROW($A$2:$A$8)-ROW($A$2)+1),ROW(1:1)) and press Shift+ Ctrl+ Enter keys
Drag the Autofill handle down to copy the formula until #NUM! appears.
