Reputation: 3
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
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:
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.A1:D3
in my example) should be limited to the search range's actual address.Upvotes: 1