Is there a way to VLOOKUP with range cell is conta

Question

I'm trying to work this (without macro), of course not working on Apples and Peer:

• Plain text exemple : https://pastebin.com/3L8rzxCa

• Snip of my excel sheet: https://imgur.com/a/kkVvqZ3

I Want to check if a cell (that contains a list of goods) contains one value from a range (inventory) AND is present in stock.

I Tried :

• VLOOKUP with wildcard (not working since fruit in stock should be contained in the Basket list, not the other way around)

• Countif : works to check if cell contains indiviual value from range, but can't check if "in stock"

Maybe a CSE formula I can't craft?

I hope it's understandable... Thanks in for your help :)

Answer 1

Quite tricky, possible better ways of doing this. But here is one way:

Formula in C8

{=IF(SUMPRODUCT(--((TRANSPOSE(IF(OFFSET($A$2:$A$5,0,1)="Yes",$A$2:$A$5,"")))=TRIM(MID(SUBSTITUTE(B8,",",REPT(" ",99)),(ROW(OFFSET($C$1,,,LEN(B8)-LEN(SUBSTITUTE(B8,",",""))+1))-1)*99+((ROW(OFFSET($C$1,,,LEN(B8)-LEN(SUBSTITUTE(B8,",",""))+1)))=1),99))))>0,"Yes","No")}

Notice it's an array formula and need to be confirmed through CtrlShiftEnter

Answer 2

You could use the following:

=IF(SUMPRODUCT(ISNUMBER(FIND($A$2:$A$5,B8))*($B$2:$B$5="Yes")),"Yes","No")

The above assumes that the fruits in the list provided are all present in the list of fruits in stock.

The above also assumes that a Yes is expected as long as at least one fruit is in stock out of the provided list of fruits.

If a No is expected if any one of the fruits in the provided list is missing, then you could use the following:

=IF(SUMPRODUCT(ISNUMBER(FIND($A$2:$A$5,B8))*($B$2:$B$5="Yes"))=SUMPRODUCT(--ISNUMBER(FIND($A$2:$A$5,B8))),"Yes","No")

Answer 3

So, based on a simple example you could try something like:

=IF(AND(MATCH(E4,$A$3:$A$6,0)>0,MATCH(E4,$A$12:$A$15,0)>0),INDEX($B$12:$B$15,MATCH(E4,$A$12:$A$15,0)),"")