Vlookup or index&match from multiple columns

[MHERSI]

Hi Guys

Hope you all well

I need some support. I’m looking for a formula that looks up multiple columns for the information I need.

For example,

Let us say, I want to look up ‘hello’, and bring this back in a required cell, but this word could be in column 1,2 or 3. How do I do this? Secondly following the same pattern, how do I do the same but for word containing ‘ello as an example.

I have attached example, but please let me know if you require more details

Many Thanks
MHERSI

[Pete_UK]

You don't seem to be wanting to lookup - rather, you seem to be wanting to see if the (sub-)string exists in any of your three columns, and if so then repeat the string. In that case you can do this in B2:

=IF(COUNTIF(C:E,"*"&A2&"*"),A2,"")

then copy down.

Hope this helps.

Pete

[MHERSI]

Thank you Pete.

you have given me exactly what i was looking for, however, is it also possible to bring back words that contain?

for example

the first word could be 'ove' and i want returning 'oversee.

Many Thanks
MHERSI

[JeteMc]

Try pasting the following into cell B2 and then dragging the fill handle down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[MHERSI]

Thanks for your support JeteMc

The formula works perfect

M Hersi

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.