Index Match with one criterium in multiple columns

[Arjen Bakker]

Hello together,

unfortunately I have to work with databases like below. People work on days 1 - 5 and do
different activities
I would be glad to be able to have the data like written in column "J" without workarounds.
In this case people who worked on day 3 with activity "c".

Thanks very much for your help !
Arjen

	A	B	C	D	E	G	H	I	J
1	Code1	Code2
2	3	c							Name
3									Wished
4	Code1	Code2	Name		Result
5	1	2	3		5	c	A		A
6	1	2		4		a	A		NA
7		2	3	4	5	c	C		C
8	1				5	d	C		NA

<colgroup><col width="64" style="width: 48pt;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" span="2"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <tbody> </tbody>

 

[AhoyNC]

Try:
Copy formula down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Code1	Code2
2	3	c							Name
3									Wished
4	Code1					Code2	Name		Result
5	1	2	3		5	c	A		A
6	1	2		4		a	A		NA
7		2	3	4	5	c	C		C
8	1				5	d	C		NA
Sheet

 

[Arjen Bakker]

Try:
Copy formula down as needed.

	A	B	C	D	E	F	G	H	I
1	Code1	Code2
2	3	c							Name
3									Wished
4	Code1					Code2	Name		Result
5	1	2	3		5	c	A		A
6	1	2		4		a	A		NA
7		2	3	4	5	c	C		C
8	1				5	d	C		NA

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 28px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula I5 =IF(SUMPRODUCT((A5:E5=$A$2)*(F5=$B$2)),G5,"NA") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hello Jeaníe,
It works.
Great again !
Thanks so much.
Have the best weekend of this week.
That's where I'm going now.
It's 3:45 AM - a bit too late to keep working,

Arjen