# excel formula -search a range

1. ## excel formula -search a range

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

2. ## Re: excel formula -search a range

Does the following in H5 entered as an array formula with ctrl-shift-enter then copied down to H8 do what you want?

Formula:

3. ## Re: excel formula -search a range

Geoff
Thanks the formula works great. However I have one variation
I have tried to nest conditions on your formula so it would not
seem to get the right combination. Also your formula works
ok without doing ctrl-shift-enter
Thanks Carroll

4. ## Re: excel formula -search a range

Try the following in H5:
Formula:

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.

5. ## Re: excel formula -search a range

Geoff
Thanks the formula works great. However I have one variation
I have tried to nest conditions on your formula so it would not
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

6. ## Re: excel formula -search a range

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

7. ## Re: excel formula -search a range

Originally Posted by carrollm
Geoff
However I have one variation
I have tried to nest conditions on your formula so it would not
seem to get the right combination.
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:

8. ## Re: excel formula -search a range

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

9. ## Re: excel formula -search a range

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:

Isn't this what you are expecting?

10. ## Re: excel formula -search a range

You are right Geoff - formula in your post 7. my misunderstanding.
Thanks much

11. ## Re: excel formula -search a range

Another non-array formula:

Just Enter

12. ## Re: excel formula -search a range

Originally Posted by carrollm
You are right Geoff - formula in your post 7. my misunderstanding.
Thanks much
bebo021999's formula is a different approach but gets the same results. Now you have two choices! Thanks for the rep

There are currently 1 users browsing this thread. (0 members and 1 guests)