counting all matched results using vlookup

[Emphyr]

Good day,

Title maybe not describing what I mean

I have many tables with different customers
Customers are repeating in different tables many times
I need to fullfil by hands result table where written which tables containing exact customer

I tryed through vlookup, but all I got is a first matched tables, but I need all matching tables customers match

Please help

Example below

[TABLE="width: 420"]
<tbody>[TR]
[TD][TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table №[/TD]
[TD]Customer[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result Table[/TD]
[TD]Customer[/TD]
[TD]Table №[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD]1,2,3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD]3,4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c[/TD]
[TD]1,2,3,4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]d[/TD]
[TD]2,3,4[/TD]
[/TR]
[TR]
[TD]Table №[/TD]
[TD]Customer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]e[/TD]
[TD]1,4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table №[/TD]
[TD]Customer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table №[/TD]
[TD]Customer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 420"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[CalcSux78]

Welcome to the forum!

Here's a potential solution:

Excel 2010

	A	B	C	D	E
Customer	Customer	Table
a	a
c	b
e	c
d
Customer	e
a
c
d
Customer
a
b
c
d
Customer
b
c
d
e
e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1,2,3,[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3,4,[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1,2,3,4,[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2,3,4,[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1,4,[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=REPT("1,",--(COUNTIF(Table1[Customer],[@Customer])> 0))&REPT("2,",--(COUNTIF(Table2[Customer],[@Customer])> 0))&REPT("3,",--(COUNTIF(Table3[Customer],[@Customer])> 0))&REPT("4,",--(COUNTIF(Table4[Customer],[@Customer])> 0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[CalcSux78]

Note for post #2 :

I formatted each of the table areas as a table (found on the home ribbon within XL)

 

[Mike Szczesny]

Can you use something like this? This is a two step process. The first formula finds the numbers for a given letter. The second formula puts all these letters in a cell for that number. I used your example. Assume F2 contains the formula to find the numbers for a given letter. Assume C2:C6 is letter range in your results table.This formula is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($A$2:$A$18,SMALL(IF($B$2:$B$18<>"",IF($B$2:$B$18=$C$2,ROW($A$2:$A$18)-ROW($A$2)+1)),ROWS($F$2:F2)))," ") D2:D6 will be your number results. In D2 use =concatenate(transpose(your number range which may vary in size&" , ")) Before you enter, you need to highlight the transpose(......) part of your formula with your mouse. Hit F9. Remove both { and }. Hit enter. My sample shows the results for letter a only. But you should get the idea I am doing.

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Table[/TD]
[TD="width: 64"]Customer[/TD]
[TD="width: 64"]Customer[/TD]
[TD="width: 64"]Table[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]1 , 2 , 3 , [/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]c[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]e[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]a[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[/TD]
[/TR]
</tbody>[/TABLE]