Problem with range

[bignationbignation]

Hi,

I have a list of desriptions about stocks (shares). I want to find out
if the descriptions contain a stock symbol from a list. I have been
given the formula below which returns "true" or "false. I would
preferably like a formula that: if the description contains a symbol
from the list, to display which symbol from the list it contains. If
the descriptions doesn't contain a symbol from the list, then display
something else like "false". I would refer not to have an array
formula as I have a long list of descriptions.

Hope this makes sense? Below is the formula I'm using.



=SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$5,B4)))>0

Many Thanks

 

[Guest]

The VLOOKUP or MATCH functions may help but it's not clear from your question
what you're trying to do?

VLOOKUP: If you want to enter a stock symbol, then have Excel look in a
defined table to see if that stock symbol is there, and then return data
based on the location of that stock symbol in the table, then use this. See
here for info: http://www.techonthenet.com/excel/formulas/vlookup.php

MATCH: If you want to enter a stock symbol, then have Excel determine
whether the same value appears in a defined table, then use this. See here
for info: http://www.techonthenet.com/excel/formulas/match.php

Dave

 

[Bob Phillips]

=INDEX(I1:I5,MATCH(TRUE,ISNUMBER(FIND(I1:I5,A2)),0))

where I1:I5 is the symbol list. And guess what, it is an array formula.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[bignationbignation]

=INDEX(I1:I5,MATCH(TRUE,ISNUMBER(FIND(I1:I5,A2)),0))

where I1:I5 is the symbol list. And guess what, it is an array formula.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -

Thanks for that. But I'd love it if someone could give me a formula
that isn't an array. This would save me a lot of time.

 

[Bob Phillips]

Why would it save time?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[bignationbignation]

Why would it save time?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)






- Show quoted text -

As I have a long list of descriptions and that means I'd have to do
CTRL SHIFT ENTER 100's of times? Unless there is a way of doing it to
many cells. Or am I just being stupid?

 

[Bob Phillips]

All you do is enter the formula alongside the first description, adjusting
the ranges I gave to your real ranges, and making them absolute

=INDEX($I$1:$I$5,MATCH(TRUE,ISNUMBER(FIND($I$1:$I$5,A2)),0))

and array-enter it, then just copy it down to the other cells alongside the
other descriptions - no need to keep re-entering.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[bignationbignation]

All you do is enter the formula alongside the first description, adjusting
the ranges I gave to your real ranges, and making them absolute

=INDEX($I$1:$I$5,MATCH(TRUE,ISNUMBER(FIND($I$1:$I$5,A2)),0))

and array-enter it, then just copy it down to the other cells alongside the
other descriptions - no need to keep re-entering.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

You ARE a life saver! Thanks SO much. It sounds simple when you know
how to do it!

Thanks again.