I have 2 sheets with values like :

Sheet 1, column D : SUPER iPhone X 16GB
Sheet 2, column B : iPhone X

I would like, in Sheet 1, to display the matching value from Sheet 2 (in this example "iPhone X")

I have tried many Vlookup like this but cannot find the right one :

=VLOOKUP("*"&D6&"*";Sheet 2!B:B;1;FALSE)

As requested I have created a sample spreadsheet with 2 sheets and sample data you can test here : https://docs.google.com/spreadsheets/d/1OHsS5SyvzHvJaRtqKXVmCSSDeKA3iqX5JC2qE8WHPw0/edit?usp=sharing

CodePudding user response：

Try

=ArrayFormula(QUERY(IF(ISERROR(FIND(Sheet 2!B:B;D6));;Sheet 2!B:B);"select Col1 where Col1 is not null limit 1";0))

CodePudding user response：

try:

=VLOOKUP("*"&D6&"*"; 'Sheet 2'!B:B; 1; 0)