How to return row matching value in filtered data?

[freddylem]

Hi everyone,

I have a filtered table, and I need to find the first row which matches a value. However "MATCH" formula will return the row of first match in all the data not just the filtered data. I'm a little stumped by this one and can't figure it out.

Any recommendations?

Thanks in advanced.

 

[istiasztalos]

Hi,

SUBTOTAL and AGGREGATE functions ignore hidden rows, for SUBTOTAL function #3 is COUNTA, you can combine this with a MATCH to find which row has your match, use it to create an indirect range reference and use SUBTOTAL,3 to count how many non-empty cells are visible between the top of your table and the identified match

In an example where what you search is in A1:A13, it would look like this

=SUBTOTAL(3,INDIRECT("A2:A"&MATCH(B18,A1:A13,0)))

If your table doesn't start at row one, you'll need to add the count of extra rows above your table to the result of the MATCH