Search an Item from unsorted Worksheet

[Mrinal]

I have prepared one organization chart in Excel and now I want to find ID and get their corresponding details by OFFSET() function. U can understand it is not ordinary data base of list format, my ID is available in all column and rows. It is easy to find with Ctrl+F, but please suggest me how to use MATCH() function or similar way to know the location of the ID in the entire worksheet so that I can use the OFFSET function.
Regards,
Mrinal

 

[Mrinal]

this will return the row number of the last text cell in column A. Similar formulas can be used for columns and numbers.

=MATCH("zzzzzz",A:A)

Sir,
Your formula is correct, in blank column only is coming #N/A otherwise it is showing the last used Row or column. Please make one array formula using ISBLANK to avoid the #N/A and get the MAX of that result.

 

[mikerickson]

Applying that formula to your longest column will give you the size of the rows in your data range. Something like
If row 3 and column C are your longest, a formula like this should work

=Sheet1!$A$1 : INDEX(Sheet1!$C:$C,MATCH("zzzz",Sheet1!$C:$C),1) : INDEX(Sheet1!$3:$3,1,MATCH("zzzz",Sheet1!$3:$3))

 

[Mrinal]

Applying that formula to your longest column will give you the size of the rows in your data range. Something like
If row 3 and column C are your longest, a formula like this should work

=Sheet1!$A$1 : INDEX(Sheet1!$C:$C,MATCH("zzzz",Sheet1!$C:$C),1) : INDEX(Sheet1!$3:$3,1,MATCH("zzzz",Sheet1!$3:$3))

Problem is that it not possible to know which is the longest row and column, to identify this we have to check the range manually every time, which I want to avoid. If you are unable to provide solution means there is no solution except VBA code.

 

[Mrinal]

.

 

[Mrinal]

Applying that formula to your longest column will give you the size of the rows in your data range. Something like
If row 3 and column C are your longest, a formula like this should work

=Sheet1!$A$1 : INDEX(Sheet1!$C:$C,MATCH("zzzz",Sheet1!$C:$C),1) : INDEX(Sheet1!$3:$3,1,MATCH("zzzz",Sheet1!$3:$3))

Sir,
Can you please give me the VBA code for the following formula which I have used as per your suggestion.
{=INDEX(Sheet1!$A$1:DataRange, MAX(IF(DataRange="ID",ROW(DataRange)))+2, MAX(IF(DataRange="ID",COLUMN(DataRange))))}
It will help me to use VBA code to my file to solve other problems. With the above formula I am finding the ID getting corresponding data below two rows.