Search rows to find specific text and move to a different column ?

I'd recommend posting a small example of what you're working with. You can attach files by going to Go Advanced, and then scrolling down to Manage Attachments.

Are you trying to dump all cells that have PE into a single cell of I?

So if J2 = PE123 and M5 = PE999 should I5 = PE123PE999?

If you're only looking for one match, it would be: =LOOKUP(1,1/SEARCH("PE",J2:AQ2),J2:AQ2) copied down

But as I (poorly) pointed out in my example, will there be occasions when there's more than one PE per row?

Try the formula I provided above and let me know if that works out.

Then likely a variation of the SMALL function. I will see if I can find it.

Edit: Found it

=IFERROR(INDEX($B$1:$E$1 (the range of the column headers)
,SMALL(IF(OFFSET($B$2:$E$2 (the range of columns which we're offsetting)
,MATCH($A10 (what we're looking for)
,$A$3:$A$6 (where we're looking for it)
,0),0)<>"",COLUMN($A$1:$D$1 (how many columns you're looking at, this means 1-4)
-ROW(A$1)+1),COLUMN(A$1))),"") (the rest of this will remain the same)

Let me try to translate this from my old project

I2:

=IFERROR(INDEX($L2:$AQ2,SMALL(IF(ISNUMBER(SEARCH("PE",$L2:$AQ2)),COLUMN($A$2:$AH$2)-ROW(A$1)+1),COLUMN(A$1))),"")

This is an Array formula, confirmed with Ctrl+Shift+Enter to exit the cell. This will put { } around the formula.

Copy down and over.

Fortunately, I leave myself a lot of notes and bookmarks for when I forget this stuff. https://www.excelforum.com/showthread.php?t=987015

Like I said, copy down and over. The formula is built to accommodate all three columns.

The relative references near the end of the formula will adjust themselves as the formula is copied into K and L to:

)-ROW(B$1)+1),COLUMN(B$1))),"")

)-ROW(C$1)+1),COLUMN(C$1))),"")

Copy the formula from your I2 exactly as you have it. I think you may have added some extra absolute references.

I'd highly recommend putting together a small sample of your spreadsheet with 9-10 rows of your data, and I'll put the formula in a way that works.

Ok, I changed and removed some stuff. Please see attached, and many thanks again !

It's good you attached a sample, because I wouldn't have never figured out it was all a simple math error. (I can't count apparently.)

Also, make sure you only select 1 cell when you enter the array formula, or you will run into the error that pops up.

So, I2:

=IFERROR(INDEX($L8:$AQ8,SMALL(IF(ISNUMBER(SEARCH("PE",$L8:$AQ8)),COLUMN($A$2:$AF$2)-ROW(A$1)+1),COLUMN(A$1))),"")

Then drag it over to K2

Then drag I2:K2 down as far as needed


The A2:AF2 part of the formula is the same as the range you're looking in, but dragged to the left. So if you were looking in D2:G2 (4 cells), the part of the formula that says A2:AF2 would becomes A2:D2 (still 4 cells). In the formula up there now, you are looking in L8:AQ8 (32 cells), so we need to make sure the COLUMN part is the first 32 cells A2:AF2.

Cool, glad it all worked out. It was my pleasure.

Come back anytime.

Remember, the Column() part has to match the number of Columns in the range you're choosing.

N2:BR2 = 57 Columns

Therefore, the Column() range has to include 57 columns, starting with A.


=IFERROR(INDEX($N2:$BR2,SMALL(IF(ISNUMBER(SEARCH("9*",$N2:$BR2)),COLUMN($A$2:$BE$2)-ROW(A$1)+1),COLUMN(A$1))),"")

because A2:BE2 = 57 COLUMNS

I never saved a copy of your worksheet since you said it was sensitive information, but you might also want to try using LEFT instead of SEARCH:

LEFT(range) pulls the first item in the string

=IFERROR(INDEX($N2:$BR2,SMALL(IF(LEFT($N2:$BR2)="9",COLUMN($A$2:$BE$2)-ROW(A$1)+1),COLUMN(A$1))),"")