combine "search" and "or"

[goldfield]

Hi Gurus,

Hoping you can assist with the below.

I am trying to add the "or" function into a much larger string and it is currently returning the #Value error.

Can you please advise if it is possible and if so, where am I getting it wrong?

Excel 2003.

=(SEARCH(OR("01_EA","01_AM"),$F$1490))

Thanks in advance.

G

[p24leclerc]

No, you can't use the Or in the Search function. First, you don't use the Or function properly. Second, the Search function would look for either True or False in your text instead of your desired search text.

Try this formula instead:

Please Login or Register  to view this content.

A little more complex but it should work. I didn't know what you wanted as output so I put Found as result.

[goldfield]

Hi Pierre,
Thanks for the response. I have included an example which may shed some more light on what I am trying to achieve. I have a series of data in column A. The formula and results in column B are based on the first 5 or 6 characters of column A. I now have different combinations that will return the same results hence the "OR" question. Being very much an excel learner, I am sure there are better ways to achieve this? Would it be an option to carry out a lookup based on the first 6 characters of a cell, with the reference table as attached in the example?
Regards,

G

[p24leclerc]

Hi,
I've put this formula in cell C4 to test it against yours.

Please Login or Register  to view this content.

There are some differences but you'd be able to tell if it works for you or not.

[bebo021999]

There are difference between mine and your manual desired output, but the formula look at whatever being seen in reference table:

B4:

=LOOKUP(2,1/IFERROR(SEARCH(FDCTasks,A4,1),""),$E$4:$E$10)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[p24leclerc]

I forgot to tell you I was using your reference table in cells D4:E10

[p24leclerc]

If the function IFERROR works in Excel 2007, then you can use this formula:

Please Login or Register  to view this content.

[FlameRetired]

Try this formula in B4:B11

Please Login or Register  to view this content.

Edit I missed the 2003 in your profile. This formula instead.

Please Login or Register  to view this content.

[goldfield]

Works perfectly -

Thankyou much appreciated!