help writing a possibly complex formula?

[AshleyT]

Hello excel Gurus,

I'm looking for help with writing a formula in a current workbook.
I need a formula that will look up information in multiple tabs, and return one or multiple values into another tab.

Below is a simplified table that I have in one tab of my workbook titled "Summary Tab" I would need the name of "Display A, Display B, Display C, etc., " to show up in the corresponding month and also in the corresponding Customers row (Customer 1, 2, 3,...)

SUMMARY TAB
[TABLE="width: 500"]
<tbody>[TR]
[TD]Q1[/TD]
[TD]January Ship[/TD]
[TD]February Ship[/TD]
[TD]March Ship[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]((would need formula HERE))[/TD]
[TD]((would need formula HERE))[/TD]
[TD]((would need formula HERE))[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]((would need formula HERE))[/TD]
[TD]((would need formula HERE))[/TD]
[TD]((would need formula HERE))[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]((would need formula HERE))[/TD]
[TD]((would need formula HERE))[/TD]
[TD]((would need formula HERE))[/TD]
[/TR]
</tbody>[/TABLE]


DISPLAY A Tab (which I have for Display A, B, C, D, E, F, G, H, I and J all in separately labeled tabs)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Ship Month[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]February
[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]January[/TD]
[/TR]
</tbody>[/TABLE]

DISPLAY B Tab

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Ship Month
[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]February[/TD]
[/TR]
</tbody>[/TABLE]



So for instance:
I have Display A
Customer 1 ship month is January
Display B
Customer 1 ship month is also January





I would need a formula that could automatically fill in, Customer 1, January Ship cell with "Display A and Display B""Display A and Display B"
To tell me that Customer 1 has two different displays shipping in the Month of January.
I would be manually entering in the ship months on each Display tab (please note for formula purposes, the ship months are volatile and I may be changing them more than once - if that matters?- )

Thank you for any help with guiding me in the right direction of how to tackle this!!!

 

[estevaoba]

Assuming Customers are listed in A2 down.
And months are listed in row 1, starting at B1 as January (not January Ship).

Try this in B2 and copy across and down:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IF('Display  A'!$B2="","",TEXTJOIN(" and  ",TRUE,IF(INDEX('Display A'!$B$2:$B$20,MATCH(Summary!$A2,'Display  A'!$A$2:$A$20,0))=Summary!B$1,"Display  A",""),IF(INDEX('Display B'!$B$2:$B$20,MATCH(Summary!$A2,'Display  B'!$A$2:$A$20,0))=Summary!B$1,"Display B","")))[/TD]
[/TR]
</tbody>[/TABLE]