Pull multiple values for a single cell with VLOOKUP (and with wildcard)

Hi, hope someone can help me with a fancy Vlookup.

I have a column with values (unique). Need to pull the matching values using VLOOKUP from a table.
The value would be as usual in column 1 of the vlookup table, but it could be found in multiple rows and it will be positioned anywhere in the string (wildcard)
***********
Example - my value to search for is 123
In the Vlookup table it could be present in one or multiple rows and anywhere in the string: Row1: ABC123 Row2: A123XYZ Row3: 123CDE
I know for sure that the exact match is not found (did regular vlookup).
And I was able to create a VLOOKUP which brings back a wildcard value but only one, the first hit.
In the example above, it will return only ABC123

How do I pull all matching values (with wildcard)?
To enhance the question - once I find a fuzzy match, I also need to pull a value from another column on the Vlookup table
Please see attachment

Thank you so much in advance

If you mean it that you're using Excel 2007, then this would be much more efficient with user-defined functions implemented in VBA. Can you use VBA?

Originally Posted by hrlngrv

If you mean it that you're using Excel 2007, then this would be much more efficient with user-defined functions implemented in VBA. Can you use VBA?

Unfortunately, I don't know it.

Try in B2 and copy across and down:

Formula:

Please Login or Register  to view this content.

Good luck!

@Bo_Ry, Hello. For what this part IF(ISERROR(SEARCH($A2;$H$3:$H$8;1));COUNTA($H$3:$H$8)+1;1) in B2?
Why not simple IF(ISNUMBER(FIND($A2;$H$3:$H$8));ROW($H$3:$H$8)-2)?

Oh sorry. That's ok :-)

Thank you very much, it helped!