Excel - Return rows w/ column containing certain value

I want rows to be returned in another sheet that contain a certain value in a specified column. It would also be beneficial to choose which columns will be returned in that row. I tried this with VLOOKUP but I can't seem to figure it out. Thanks.

EDIT:

I apologize for the minimum details, so here's what I'm trying to do. I have a project name, priority #, project status, details, and various miscellaneous columns. I have my projects assigned a priority number from 1-8. In another sheet, I want to look through all of the priority columns, find each row that has a priority # of 2, then return from that row the project name and project status to their own column.

EDIT2:

I'd also like to omit any rows that don't contain the #2.

1 Answer

I would like to suggest a Lookup Formula will pull all the Rows from one sheet to other. Remember I've designed the Data set on the basis of information provided by you only.

For better understanding I've illustrated both Input & Output side by side. But you need to execute it in Sheet2.

=INDEX($A$2:$D$8, SMALL(IF(COUNTIF($E$2, $A$2:$A$8), ROW($A$2:$D$8)-MIN(ROW($A$2:$D$8))+1), ROW(A1)), COLUMN(A1))

NB: Note few things before you exercise it.

1. Data set in Black colour is Sheet1.
2. Columns are A to D.
3. Green colour Data set is Sheet2 (including the 2 in RED).
4. Columns are E to I.
5. In Cell E2 (Red colour)in Sheet2 you may put Priority nu. to Filter related records from sheet1.
6. Formula Cell is F2 in Sheet2.
7. Write the Formula and first drag it Right then Down.
8. Don't forget to add Sheet1 with Cell Address in the FORMULA.

Hope this help you.