Returns the title according to the values in the database

[Omer_K]

Hello friends
I have a database with a date column, the customer's name in the header - when that column has values and the country of that customer,
At the bottom of the sheet there is a small database with date, country and value and I am interested in returning the customer name - in the sheet I attached I am interested in finding the cell range: H16: H22
Attach you a sample sheet - hope I was clear:

Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	client 1	Country	client 2	Country	client 3	Country	client 4	Country	client 5	Country	client 6	Country
2	06/10/2021	78	Italy	4	Germany	11	Netherlands	5	Germany	99	Italy	17	Argentina
3	07/10/2021	78	Italy	4	Germany	11	Netherlands	5	Germany	99	Italy	17	Argentina
4	13/10/2021	78	Italy	4	Germany	11	Netherlands	5	Germany	99	Italy	17	Argentina
5	14/10/2021	78	Italy	4	Germany	11	Peru	3	Germany	99	Italy	17	Argentina
6	20/10/2021	80	Italy	4	Argentina	11	Peru	3	Germany	99	Italy	17	Argentina
7	21/10/2021	80	Italy	4	Argentina	12	Peru	3	Germany	99	Netherlands	17	New Zealand
8	27/10/2021	80	Italy	4	Argentina	12	Peru	3	Argentina	99	Netherlands	17	New Zealand
9	28/10/2021	78	Germany	4	Argentina	12	Peru	3	Argentina	54	Netherlands	17	New Zealand
10	03/11/2021	78	Germany	4	Argentina	12	Peru	3	Argentina	54	Netherlands	17	New Zealand
11	04/11/2021	78	Germany	4	Argentina	12	Peru	3	Argentina	54	Netherlands	17	New Zealand
12
13
14
15					Date	Country	No.	Client
16					21/10/2021	Germany	3	client 4
17					21/10/2021	Netherlands	99	client 5
18					28/10/2021	New Zealand	17	client 6
19					03/11/2021	Argentina	3	client 4
20					21/10/2021	Italy	80	client 1
21					07/10/2021	Netherlands	11	client 3
22					14/10/2021	Germany	4	client 2
Data

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Omer_K]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

My version is Microsoft 365 For Biz

 

[Fluff]

Thanks for that, if numbers in the client column are never repeated on a particular row, as per your sample, try

=INDEX($B$1:$M$1,MATCH(G16,FILTER($B$2:$M$11,$A$2:$A$11=E16),0))

 

[Omer_K]

Thanks for that, if numbers in the client column are never repeated on a particular row, as per your sample, try

=INDEX($B$1:$M$1,MATCH(G16,FILTER($B$2:$M$11,$A$2:$A$11=E16),0))

Th

Thank you Fluff ?
The problem is the client column are repeated on a particular row
So should also contain the number..

 

[Fluff]

That formula is looking for the number. Are you saying that the same number might appear more than once is a row?

 

[Omer_K]

That formula is looking for the number. Are you saying that the same number might appear more than once is a row?

Absolutely yes, it can be repeated several times in the same row

 

[Fluff]

Ok, in that case how about

=INDEX($B$1:$M$1,AGGREGATE(15,6,(COLUMN($B$1:$M$1)-COLUMN($B$1)+1)/($A$2:$A$12=E16)/($B$2:$L$12=G16)/($C$2:$M$12=F16),1))

 

[Omer_K]

Ok, in that case how about

=INDEX($B$1:$M$1,AGGREGATE(15,6,(COLUMN($B$1:$M$1)-COLUMN($B$1)+1)/($A$2:$A$12=E16)/($B$2:$L$12=G16)/($C$2:$M$12=F16),1))

First of all - i'm so appreciate your help (and your genius ?)
I'm running the formula and I'm having another problem where it could be that the date, value and country are repeated by some customers - so I need him to show me all the customers
I showed you the example in the database below:

Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	client 1	Country	client 2	Country	client 3	Country	client 4	Country	client 5	Country	client 6	Country
2	06/10/2021	78	Italy	4	Germany	11	Netherlands	11	Netherlands	99	Italy	17	Argentina
3	07/10/2021	78	Italy	4	Germany	11	Netherlands	11	Netherlands	99	Italy	17	Argentina
4	13/10/2021	78	Italy	4	Germany	11	Netherlands	5	Germany	99	Italy	17	Argentina
5	14/10/2021	78	Italy	4	Germany	11	Peru	3	Germany	99	Italy	17	Argentina
6	20/10/2021	80	Italy	4	Argentina	11	Peru	3	Germany	99	Italy	17	Argentina
7	21/10/2021	80	Italy	4	Argentina	12	Peru	3	Germany	99	Netherlands	17	New Zealand
8	27/10/2021	80	Italy	4	Argentina	12	Peru	3	Argentina	99	Netherlands	17	New Zealand
9	28/10/2021	78	Germany	4	Argentina	12	Peru	3	Argentina	54	Netherlands	17	New Zealand
10	03/11/2021	78	Germany	4	Argentina	12	Peru	3	Argentina	54	Netherlands	17	New Zealand
11	04/11/2021	78	Germany	4	Argentina	12	Peru	3	Argentina	54	Netherlands	17	New Zealand
12
13
14
15					Date	Country	No.	Client
16					21/10/2021	Netherlands	11	client 3
17					21/10/2021	Netherlands	11	client 4
18					28/10/2021	New Zealand	17	client 6
19					03/11/2021	Argentina	3	client 4
20					21/10/2021	Italy	80	client 1
21					07/10/2021	Netherlands	11	client 3
22					14/10/2021	Germany	4	client 2
Data

Cell Formulas
Range	Formula
H18:H22	H18	=INDEX($B$1:$M$1,AGGREGATE(15,6,(COLUMN($B$1:$M$1)-COLUMN($B$1)+1)/($A$2:$A$12=E18)/($B$2:$L$12=G18)/($C$2:$M$12=F18),1))

 

[Fluff]

How about

=INDEX($B$1:$M$1,AGGREGATE(15,6,(COLUMN($B$1:$M$1)-COLUMN($B$1)+1)/($A$2:$A$12=E16)/($B$2:$L$12=G16)/($C$2:$M$12=F16),COUNTIFS(E$16:E16,E16,F$16:F16,F16,G$16:G16,G16)))