excel is there a formulae to do this?

Occasional Visitor

Hello I am trying to make my life a little bit easier, I have several sheets that I would like to extract an entire row using specific criteria in one cell. I am not tech savvy so coding and macros are not an option would I be able to achieve this using a formula?  

The criteria is NT in cell block F from multiple sheets for example the picture shows 00GM14 and i would like to put all rows that read NT into the sheet labeled NT

 

Matthew6770_0-1642113501193.pngMatthew6770_1-1642113528446.png

Many Thanks in advance for any responses even if it is an impossibility as I have ran out of ways to search for the function required that doesn't seem like complete gibberish to me and havent found a yes or no answer yet. 

3 Replies
Have you tried doing a VLOOKUP? It is my go-to function in Excel.

@Matthew6770 

=INDEX('00GM14'!A$2:A$6,SMALL(IF('00GM14'!$F$2:$F$6="NT",ROW($A$2:$A$6)),ROW(1:1))-1)

 

This formula works for data from one sheet as shown in the attached file. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

 

@Matthew6770 

= FILTER(Table1, Table1[faul state]="NT")

You didn't mention which version of Excel.  The above works in Excel 365 and 2021 by converting the source data to a Table and applying the FILTER function.  I was happy to finish with traditional spreadsheet methods, but others feel differently.