PDA

View Full Version : [SOLVED] Formula to return two multiple lookups in one cell ?



ashleyuk1984
07-31-2019, 06:02 PM
Hi,

Please take a look at the screenshot below.

24718

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.

24719

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
08-01-2019, 04:22 AM
Array-enter:
=TEXTJOIN(" / ",TRUE,IF((($B$2:$B$6=F2)*($C$2:$C$6=G2))=1,$A$2:$A$6,""))

ashleyuk1984
08-01-2019, 07:44 AM
Perfect p45cal
Thank you very much.