Formula to return two multiple lookups in one cell ?

[ashleyuk1984]

Hi,

Please take a look at the screenshot below.

pic1.jpg

The list on the left consists of containers and products - listed individually

The list on the right consists of a unique description and package type combo.

I'm now trying to put the associated containers onto the right hand side list. See highlighted cells.

This is what I'm trying to achieve. Can this be done with standard formulas? BTW, I have access to the new dynamic array formulas, so that could also be an option.

I've attached a copy of a dummy workbook.

lookup - single cell.xlsx

Just to let you know, I've tried this formula:

=TEXTJOIN(" / ",TRUE,IF(F2=$B$1:$B$6,$A$1:$A$6,""))

Which retrieves the containers based on one criteria... But upon attempting to use another criteria such as:

=TEXTJOIN(" / ",TRUE,IF(AND(F2=$B$1:$B$6,G2=$C$1:$C$6),$A$1:$A$6,""))

Gives me a blank cell?


Thanks

[p45cal]

Array-enter:
=TEXTJOIN(" / ",TRUE,IF((($B$2:$B$6=F2)*($C$2:$C$6=G2))=1,$A$2:$A$6,""))

[ashleyuk1984]

Perfect p45cal
Thank you very much.