Return certain rows from a separate tab if a condition is met

[Luke10101]

Hello, looking for some help.

I have a raw data tab which contains a large data set which can be grouped into 5 categories based on the attribute in column 'BB'.

I have 5 tabs which I would like to look up the value in column BB and if it is a match then pull into the tab.

Preferably I would like to stick to using a formula only and using the autofilter is not a suitable solution for me at the moment.

I alrady have additional columns that return the row number for me on the data tab - I just need a way to use an Index statement without pulling through blanks / "N/A".

Any ideas?

 

[Rijnsent]

Hi Luke,
try e.g. this manual: https://www.excel-bytes.com/how-to-...based-on-a-criteria-without-filters-in-excel/
As an alternative: you can make a column in your five sheets with the right rows. Assuming your data starts from row 2 and you put these formulas in column A:
A2: =MATCH("my_filter",YourDataSheet!$BB$2:$BB$500,0)
A3: =MATCH("my_filter",OFFSET(YourDataSheet!$BB$2:$BB$500,A2,0),0)+A2 -> drag down
Use that column in either an INDEX or a OFFSET formula to pull in the right values.
Cheers,
Koen