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





[TABLE="width: 493"]
<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>[TR]
[TD="class: xl80, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 74, bgcolor: transparent"] A [/TD]
[TD="class: xl84, width: 67, bgcolor: transparent"] B[/TD]
[TD="class: xl84, width: 58, bgcolor: transparent"] C[/TD]
[TD="class: xl84, width: 58, bgcolor: transparent"] D[/TD]
[TD="class: xl84, width: 59, bgcolor: transparent"] E [/TD]
[TD="class: xl84, width: 64, bgcolor: transparent"] G[/TD]
[TD="class: xl84, width: 74, bgcolor: transparent"] H[/TD]
[TD="class: xl84, width: 55, bgcolor: transparent"] I[/TD]
[TD="class: xl84, width: 82, bgcolor: transparent"] J[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: transparent"] 1[/TD]
[TD="class: xl94, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] Code1[/TD]
[TD="class: xl85, bgcolor: #FFF2CC"] Code2[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: transparent"] 2[/TD]
[TD="class: xl92, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 3[/TD]
[TD="class: xl86, bgcolor: #FFF2CC"] c[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl87, bgcolor: blue"] Name[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: transparent"] 3 [/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl89, bgcolor: blue"] Wished[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: transparent"] 4[/TD]
[TD="class: xl91, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] , colspan: 5"] Code1[/TD]
[TD="class: xl86, bgcolor: #FFF2CC"] Code2[/TD]
[TD="class: xl87, bgcolor: blue"] Name[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl90, bgcolor: blue"] Result[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: transparent"] 5[/TD]
[TD="class: xl95, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 1[/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 2[/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 3 [/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] [/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 5[/TD]
[TD="class: xl86, bgcolor: #FFF2CC"] c [/TD]
[TD="class: xl88, bgcolor: blue"] A[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl88, bgcolor: blue"] A[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: transparent"] 6[/TD]
[TD="class: xl95, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 1[/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 2[/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] [/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 4[/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] [/TD]
[TD="class: xl86, bgcolor: #FFF2CC"] a[/TD]
[TD="class: xl88, bgcolor: blue"] A[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl88, bgcolor: blue"] NA[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: transparent"] 7 [/TD]
[TD="class: xl95, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] [/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 2[/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 3[/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 4 [/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 5[/TD]
[TD="class: xl86, bgcolor: #FFF2CC"] c [/TD]
[TD="class: xl88, bgcolor: blue"] C[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl88, bgcolor: blue"] C[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: transparent"] 8[/TD]
[TD="class: xl95, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 1[/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] [/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] [/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] [/TD]
[TD="class: xl93, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBDBDB]#DBDBDB[/URL] "] 5[/TD]
[TD="class: xl86, bgcolor: #FFF2CC"] d[/TD]
[TD="class: xl88, bgcolor: blue"] C[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl88, bgcolor: blue"] NA[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

<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>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Code1[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Code2[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]c[/TD]

[TD="bgcolor: #C0C0C0, align: center"]Name[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #C0C0C0, align: center"]Wished[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Code1[/TD]
[TD="bgcolor: #C0C0C0"] [/TD]
[TD="bgcolor: #C0C0C0"] [/TD]
[TD="bgcolor: #C0C0C0"] [/TD]
[TD="bgcolor: #C0C0C0"] [/TD]
[TD="bgcolor: #C0C0C0, align: center"]Code2[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Name[/TD]
[TD="bgcolor: #C0C0C0"] [/TD]
[TD="bgcolor: #C0C0C0, align: center"]Result[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]A[/TD]

[TD="bgcolor: #FFFF00, align: center"]A[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]

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

[TD="align: center"]a[/TD]
[TD="align: center"]A[/TD]

[TD="bgcolor: #FFFF00, align: center"]NA[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]C[/TD]

[TD="bgcolor: #FFFF00, align: center"]C[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]C[/TD]

[TD="bgcolor: #FFFF00, align: center"]NA[/TD]

</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