CODEWITHSUNDEEP
excel
Rob
Rob

Reputation: 3

In Excel how can I return the address of a cell based on the value in a sheet without previously limiting to a certain column or row?

enter image description here

In my example above I have a 4 by 3 table and I want the cell address of T but I do not know in which row or column of the 4 by 3 table the value could be located just that it is within the table. What excel formula will return the address? Assume there are too many rows and columns to use if functions to search one row or column at a time with a series of if then statements. All values within the table are unique

Upvotes: 0

Views: 193

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

use INDEX and skip getting the address:

=INDEX(Sheet2!A:D,AGGREGATE(15,7,ROW(A1:D3)/(A1:D3="t"),1),AGGREGATE(15,7,COLUMN(A1:D3)/(A1:D3="t"),1))

A couple of notes:

  1. The INDEX range (Sheet2!A:D in my example) should always start in column A even if the data is in different column and should include the full columns to the end of the data set in the other sheet.
  2. The other ranges (A1:D3 in my example) should be limited to the search range's actual address.

enter image description here

Upvotes: 1

Related Questions