# excel is there a formulae to do this?

Occasional Visitor

# excel is there a formulae to do this?

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

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

# Re: excel is there a formulae to do this?

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

# Re: excel is there a formulae to do this?

=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.

# Re: excel is there a formulae to do this?

``= 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.