Data Tables - Multiple rows of data

[jarwyn]

Hi

Not sure if Data Tables are what I should be looking at to solve this
problem, perhaps I need a Macro.

I have a set of Data in rows from (including headers) A1:E400. In
column E, I have numeric values to 1 decimal point. This value is not
unique but has a lower limit of 2.0 and higher limit of 9.0.

What I am trying to do is to specify a value in my input cell of (say)
3.5 and have Excel return the contents of the entire row (A to E) for
each occurrence of that value (3.5) in my data range (A2:E400).

Can I use a Data Table to do this? Any suggestions gratefully
received.

Cheers

 

[Domenic]

Here's one approach you may want to consider...

G2: enter your criteria (input cell)

H1: enter a 0

H2, copied down:

=IF(($A2<>"")*($E2=$G$2),LOOKUP(9.9999999999999E+307,$H$1:H1)+1,"")

I1:

=LOOKUP(9.9999999999999E+306,H:H)

J2, copied down:

=IF(ROW()-ROW($J$2)+1<=$I$1,MATCH(ROW()-ROW($J$2)+1,H:H,0),"")

K2, copied across and down:

=IF(N($J2),INDEX(A:A,$J2),"")

Hope this helps

 

[jarwyn]

Hi Domenic

many thanks - yes, that seems to have done the trick. I'm still tryin
to work out what the formulae are doing and how it does it though ..
but excellent stuff. Thanks again.

Cheer