I am trying to come up with a formula that will do the following.



*I want it to search through sheet 1 Columns E through O

**I want it to look for the text HOLD

any time that it finds the word HOLD, I want it to paste the entire Row that it is found in sheet 2



not sure if this is a look up formula....or an if

anyone able to help

You don't really need a second sheet for this, you can AUTOFILTER the data right there on sheet1.

Does the word HOLD represent an entire cell contents or is it part of a longer sentence?

If column Z is empty, for instance, you could put this formula in Z2 then copy down to see if HOLD is in E2:O2

=OR(ISNUMBER(SEARCH("HOLD", E2:O2)))

...confirm that formula by pressing CTRL-SHIFT-ENTER to activate the array. Then copy that cell down the column. You now have a series of TRUE/FALSE results.

Do an AUTOFILTER on that column for TRUE and you are now looking at all the rows that fit, the others are hidden.

actually, each row is an item....and starting at column E is a step in a process with the item.

if the item is holding at the first stage in E.....a person would put HOLD....and so on. there should never be more than one HOLD listed in a row at a time.

I am able to Auto Filter....but kinda wanted to have a sheet that would just list all of the items that are currently HOLDING at any stage of our process.....and be listed on a seperate sheet for easy review.

Then in that same empty column create a HOLD KEY index. Use this formula in Z2 and copy down:

=IF(OR(ISNUMBER(SEARCH("HOLD", E2:O2))), N(Z1) + 1, N(Z1))


Now you have a unique list of index numbers going down that column.

On a separate sheet you can collect the first instance of each key.

Column A:
KEY
1
2
3
4
5
6
etc....

In column B, at B2, then copied down:

=IF(ISNUMBER(MATCH(A2, Sheet1!$Z:$Z, 0)), INDEX(Sheet1!$A:$A, MATCH(A2, Sheet1!$Z:$Z,0)), "")

This presumes the information you want to list is in column A.