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

 

[mikerickson]

The CSE formula
=MAX((A1:F20="cat")*(ROW(A1:F20)+COLUMN(A1:F20)/1000))

will return the location of the last occurance of "cat" in A1:F20.

The value will be in row.column format such that if "cat" is in B6, the value will be 6.0002 (row 6, column 0002)

If "cat" is in F4, the value returned is 4.0006 (row 4, column 6)

 

[Mrinal]

Thank you Sir. You are great. It will solve my problem.

 

[Mrinal]

Sir,

Do you have any other option without using array formula? Excel is running out of resources when I am using for larger range and using the formula in different rows. In my worksheet all IDs are unique, as MATCH() function only works in one column only, is there any other method where all the columns can be given as range.
Regards,

Mrinal

 

[mikerickson]

If you want to return the value two cells to the right of "cat", in helps to use a named range
Name: dataColumn RefersTo: =Sheet1!$B$2:$J$30

The CSE formula
=INDEX($A$1:dataRange, MAX(IF(dataRange="cat",ROW(dataRange))), MAX(IF(dataRange="cat",COLUMN(dataRange)))+2)

will return the value from the cell 2 to the right of "cat" in B2:J30

 

[mikerickson]

That formulation is flawed if there are two cells with "cat".
This should work
Name: dataRange ; RefersTo: =Sheet1!$B$2:$G$21
Name: FilteredIndex ; RefersTo: =IF(dataRange="cat",ROW(dataRange)+COLUMN(dataRange)/1000)

Then this formula will return the cell value 1 row down and 2 to the right of "cat"

=INDEX($A$1:dataRange, 1+INT(MAX(FilteredIndex)), 2+1000*MOD(MAX(FilteredIndex),1))

 

[Mrinal]

Thanks, First one is working fine for me as I have used ID only one time in the sheet. This is much Faster.

 

[Mrinal]

If you want to return the value two cells to the right of "cat", in helps to use a named range
Name: dataColumn RefersTo: =Sheet1!$B$2:$J$30

The CSE formula
=INDEX($A$1:dataRange, MAX(IF(dataRange="cat",ROW(dataRange))), MAX(IF(dataRange="cat",COLUMN(dataRange)))+2)

will return the value from the cell 2 to the right of "cat" in B2:J30

I need another help from you. I want to get the last used Row and column in the sheet so that I can use the data range accordingly. As my sheet will grow in future I can not give entire worksheet as range is it will cause memory problem in Excel. On my writing to another thread someone has suggested the following VBA code.

Sub last()

i = ActiveCell.SpecialCells(xlLastCell).Row
j = ActiveCell.SpecialCells(xlLastCell).Column

MsgBox (i & "," & j)

End Sub

It is exactly giving the Row and Column number I am asking but I do not want to attach any VBA code to my Excel sheet. If you have any other alternative like any formula to know the Last used Row and Column, Please tell me. I am waiting for your suggestion.

 

[mikerickson]

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)

 

[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)

Answer is coming #N/A on using your formula. Pl confirm anything to replace "zzzzzz". If it is possible to know the last text cell in the row or column as you said, then please make one array formula which will get MAX value among the all column and Rows in the sheet and that will solve my problem. Pl reply sir.
<table style="width: 206px; height: 20px;" border="0" cellpadding="0" cellspacing="0"><col width="206"><tbody><tr height="20"> <td class="xl63" style="height: 15pt; width: 155pt;" height="20" width="206">
</td> </tr><tr><td style="vertical-align: top;">
</td></tr></tbody></table>