I have attached a sheet
used formula =IFERROR(LOOKUP(1,-SEARCH($K$11:$K$13,$C5),"TREK "&C5),C5)
works on first line but not when copy down
I have attached a sheet
used formula =IFERROR(LOOKUP(1,-SEARCH($K$11:$K$13,$C5),"TREK "&C5),C5)
works on first line but not when copy down
Does the following in H5 entered as an array formula with ctrl-shift-enter then copied down to H8 do what you want?
Formula:Please Login or Register to view this content.
Geoff
Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select ?Solved? from the Thread Tools menu
Geoff
Thanks the formula works great. However I have one variation
If the description already contains" "trek" trek is added again.
I have tried to nest conditions on your formula so it would not
add trek if already there but can't
seem to get the right combination. Also your formula works
ok without doing ctrl-shift-enter
Thanks Carroll
Try the following in H5:
Formula:Please Login or Register to view this content.
For this to work when copied down I believe it does need to be entered as an array formula (the SEARCH($K$11:$K$13 bit of the formula is what causes the need. Certainly if you don't enter as an array formula the results are different (and I think wrong) for rows 7 & 8.
Copy H5 down to H8
Let me know whether this works for you.
Geoff
Thanks the formula works great. However I have one variation
If the description already contains" "trek" trek is added again.
I have tried to nest conditions on your formula so it would not
add trek if already there but can't
seem to get the right combination. Also your formula works
ok without doing ctrl-shift-enter
Thanks Carroll
I have attached a new sheet where I tried formula =IF(ISNUMBER(SEARCH("TREK",B2)),B2,IF(SUM(NOT(ISERROR(SEARCH($J$2:$J$5, B2)))+0)>0, "TREK " & B2, B2))
see new sheet attached it worked and did not work
Thanks
Geoff
Sorry, I think I sent the wrong sheet.
correct one is attached. I did use the ctrl-shift-enter to save and then copied down. Still get
the two instances where it does not work. Your formula works great except for when trek already occurs.
the new sheet will show this the brackets that show when ctrl-shift-enter is used also show
I'm not clear on what you are looking for in K4:K5. The above quote led me to believe that both your formula in post #4 and mine in post #5 were giving correct results.
What are your expected results for K4:K5 ?
Is it that you want to suppress adding "TREK" ONLY IF the description already STARTS with "TREK" ?
If so then try the following in K2 entered as an array formula then copied down to K8:
Formula:Please Login or Register to view this content.
j4:j6 are names that should have trek at the start of the name.
The formula k2, k3 and k7correctly added trek but did not in k4 and k5
Since "used" is not in j4:j6 then the formula did not add trek to k6
since b8 already has trek in the description, trek was not added in k8..
Hope that helps
so bottom line is, I don't understand why trek was not added in k4 and k5 since
checkpoint and madone is in j4:j6 and trek is not already in b4 and b5
Carroll
1) You are talking about j4:j6. Has the spreadsheet moved on? Relative to your post #6 attachment I think you mean j2:j5.
2) Did you try my post #7 formula in your post #6 attachment? It gives the following results:
madone1_gw_revB.png
Isn't this what you are expecting?
You are right Geoff - formula in your post 7. my misunderstanding.
Thanks much
Another non-array formula:
Just EnterPlease Login or Register to view this content.
Quang PT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks