if cell equals "value" then return that entire row to another worksheet

**juliehight** · 09-26-2012, 02:32 PM

Hello,
I have been looking all over to find this answer... I'm not sure if I'm asking the right question... It seems like it should be so simple.

I am trying to return any row (the entire row) that has "keyword" in a particular column to another worksheet.

For example,

lets say I have 2 worksheets in my book: 'data' & 'test' ('data' has all the data and 'test' is empty at this point)
'data' sheet has columns A thru Z with the "keyword" potentially residing in column E
I want to look in column E for the "keyword"
then essentially copy (*may not be copy, may be match or equal?) ANY/ALL rows (and everything in that corresponding row; columns A thru Z) that contain "keyword" in column E
and *paste those entire corresponding rows into 'test' sheet

Also, I need the cells on 'test' sheet to update whenever the corresponding cells on 'data' update.
*note: The function does not necessarily need to be copy & paste, (I just used that for my example).

I appreciate any help.
Thanks!
Julie

**TMS** · 09-26-2012, 02:44 PM

Well, if the data you want to return is after the keyword, you can use VLOOKUP. If it is before it, you will need to use INDEX/MATCH. You should be able to find lots of examples now you know what to look for.

Regards, TMS

**juliehight** · 09-26-2012, 03:12 PM

Thanks for your help TMS; but the data is before AND after... the keyword falls in the middle column (column E) on the sheet. I can't really control that since this is going to be hooked to a live data source that refreshes.

**TMS** · 09-26-2012, 03:36 PM

Assuming your key field on the Test sheet is also in column E and the data starts in row 2:

A2: =IFERROR(INDEX(DATA!A:A, MATCH($E2, DATA!$E:$E, 0),"")

Drag across to all columns except E. The column returned will auto-adjust (the column searched is absolute).

Regards, TMS