Find Text in Excel Range and Return Cell Reference (3 Ways)

How does this formula work?

• MATCH(D16,B4:B14,0), returns the value 9 because the position of Dropbox is ninth in the array B4:B14.
• The overall formula becomes:

=CELL(“address”,INDEX(B4:B14,9))

• The INDEX(B4:B14,9) part refers to cell reference B12. So, the formula becomes: =CELL(“address”,B12)
• =CELL(“address”,B12) returns an absolute reference of the cell B12.
• We get $B$12 as the output of the formula.

How does this formula work?

• The column is selected dynamically using Excel’s OFFSET function: OFFSET(B4,0,D17-1,11,1)

How does the formula work?

Cell D9 = {=SMALL(IF($D$6=$B$4:$B$14,ROW($B$4:$B$14)-ROW($B$4)+1),ROW(1:1))}

To understand this array formula clearly, you can read our guide: 5 Examples of Using Array Formula in Excel

• IF($D$6=$B$4:$B$14,ROW($B$4:$B$14)-ROW($B$4)+1) returns the array for the SMALL function.
  • Logical test part of the IF function is: $D$6=$B$4:$B$14. This part tests (one by one) whether the values of the range $B$4:$B$14 are equal to $D$6. If equal, a TRUE value is set in the array and if not equal, a False value is set in the array: {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}
  • ROW($B$4:$B$14)-ROW($B$4)+1) returns an array like this: {1;2;3;4;5;6;7;8;9;10;11} – {1} + 1 = {0;1;2;3;4;5;6;7;8;9;10} + 1 = {1;2;3;4;5;6;7;8;9;10;11}
• ROW(1:1) is actually the k of the SMALL function. And it returns 1.
• The formula in the cell D9 becomes like this: SMALL(IF({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11}),1).
• The IF function returns this array: {FALSE;FALSE;3;FALSE;FALSE;FALSE;7;FALSE;9;FALSE;FALSE}.
• The formula becomes: SMALL({FALSE;FALSE;3;FALSE;FALSE;FALSE;7;FALSE;9;FALSE;FALSE},1).
• The formula returns 3.