Search text in range, if matches, return match value

Hi Special-K,

Thanks for the reply. These don't quite fit the bill because the cell which is being used to match against the range has quite a bit of data in it, so I almost need the formula to search the cell and match if the value is in the range.

I think that make sense! Sorry, I also realise that a bit more info in my original post would have been helpful.

So lets say the below is the cell:

Please Login or Register  to view this content.

The range is List as per the below:

• AFA
• GHA
• ADD
• FFA

Once searched, I want it to return AFA, once the match is done.

Try this ...

=LOOKUP(2,1/FIND(List,A1),List)

Thanks for the reply, Phuocam. Much appreciated.

It just brings up a "0". I've put the code in as:

Please Login or Register  to view this content.

List is range F1:F5

=LOOKUP(2,1/FIND('Source Data'!$F$1:$F$5,D2),'Source Data'!$F$1:$F$5)

Not F:F.

Hi,

Thanks for that, I just realised! It works brilliantly.

However, It does still up an issue for me. The list is likely to expand, at the moment is it has 350 entries, but the about won't let me put, for example 'Source Data'!$F$1:$F$1000' to allow for expansion.

Is there any way around this?

Alt+F3 -> Name manager -> New

Name -> List

Refers to: =OFFSET($F$1,,,COUNTA($F:$F),)

Formula:

=LOOKUP(2,1/FIND(List,A1),List)

That's worked a dream.

Just before your message I tried the name manager method but was putting the name of the list in quotes, so it wasn't working, so I gave up on it, but your message corrected my mistake.

You're welcome!