Find multiple instances of a numeric value and Return row number where value is found

[Sam]

Hi All,

I am looking for a formula based solution - using Excel 2007 for Windows.

My data is numeric, in a tabular format, spanning many columns and rows – the number of rows will continue to increase. For that reason, I have created a dynamic named range called “Data” that refers to my table of data.

The layout and structure of my table “Data”:
Numeric labels in row 3, spanning the width of my table, “Data” starts in column “G” row 4 and ends in column “BK”. Column “A” contains a numeric reference that can be used as relative row numbers for my table (row 4 = table “Data” row 1). The cells within table “Data” are populated with a formula which returns either a numeric value or empty text (“”) showing a blank cell.

Scenario:
I would like to find many different duplicate criteria (values) in my table, starting with the oldest data in row 4, and working across each column and then down the rows. There may be multiple instances of the criteria in the same row. Once these criteria are found their table row number found in column “A” (not the worksheet row number) for each instance should be returned to separate cells, down a single column - using dynamic name “Data” in a formula based solution with an input cell for the criteria. In case a criterion is not found please provide an error-trap returning empty text (“”).

Sample Layout of table "Data" contained in Book8.xls - attached


Expected Results:
Looking for all criteria of 0 (zero) in table “Data” –
Found in column “I”, row 4; return table row number = 1
Found in column “K”, row 4; return table row number = 1
Found in column “T”, row 4; return table row number = 1
Found in column “AB”, row 4; return table row number = 1
Found in column “I”, row 9; return table row number = 6
Found in column “V”, row 14; return table row number = 11

Hope you can help.

[NBVC]

You need to also create a Dynamic Named Range for column A so that you can grow that column too...

After you've done that (it should be same rows as the DATA named range)... then try:

=INDEX(colA,SMALL(IF(Data=0,ROW(Data)-MIN(ROW(Data))+1),ROWS($A$1:$A1)))

where colA is dynamic named range name for column A corresponding to DATA range.

confirmed with CTRL+SHIFT+ENTER and copied down.

[Sam]

That worked brilliantly! Thank you for your time and assistance.

[NBVC]

You're welcome.

I did forget to mention that you could wrap an IFERROR() around the formula to "hide" errors as you copy down, so that your result range can be a bit dynamic too.

[Sam]

Will do.

I did forget to mention that you could wrap an IFERROR() around the formula to "hide" errors as you copy down, so that your result range can be a bit dynamic too.