EXTRACTS IF CONTAINS

[giovanni9720]

EXTRACTS IF CONTAINS

.
The goal is to get the same result obtained in column D of the sheet "daily" but with a more decent formula and that allows me to add more data to the legend without having to put my hand to the formula every time
.
What should the formula do?
Look at column B of the daily sheet
.
If it contains some word which matches column B of the legend sheet, then the formula must return the value located in column C of the legend sheet
.
I try to explain myself even better
If for example in column b of the daily sheet appears "conad" followed or preceded by other words, then the formula must return "needed"
.
Why you should extract just "necessary"? Because in the sheet "legend" next to the word "conad", located in column B, the word "necessary" appears
.
If in column B of the daily sheet appears "restaurant" followed or preceded by other words, then the formula must extract "leisure" and so on
.
Why should you draw your own "leisure"? Because in the sheet "legend" next to the word "restaurant", located in column B, appears the word "leisure"
.
I hope I explained myself a little better
Thank you very much indeed

 

[arnelgp]

you post a sample worksheet to be more clear to us.
what do you mean by "next" word in column B?

 

[giovanni9720]

my contents appear like spam, where can i link the google sheet file? thanks

 

[Gasman]

my contents appear like spam, where can i link the google sheet file? thanks

You need to be patient. It is an anti spam feature. You need a set number of valid posts to be able to post links.
Break up the 'link' with spaces for now, so it is not recognised as a link.

 

[giovanni9720]

other forum suggest me this formula that works
.
=arrayformula( SE.ERRORE( CERCA.VERT( regexextract(B5:B; textjoin("|"; VERO; leggenda!B5:B)); leggenda!B5:C; COLONNE(leggenda!B5:C); FALSO ); SE( LUNGHEZZA(B5:B); "scrivi qui a sinistra"; SE.ERRORE(1/0) ) ) )
.
you can see it in the cell giornaliero!d5 in this file
https:/ /docs.google.com/spreadsheets/d/1Sh86J7ChtibaRpNHugURH4ybBZlXDDpR9np0IQfOd9w/edit?fbclid=IwAR0qVTKQ30P6tJuql7uxO11UcSt88qPhG7Wkic2Uc77fNcY17xfcNy2JTgI#gid=799113712

i hope i will help other people who have the same problem
thank you anyway for your time