Return a specific cell if two criteria is met

[danielpeam]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Grade[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Terry[/TD]
[TD]00-01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]James[/TD]
[TD]00-01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Terry[/TD]
[TD]01-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bob[/TD]
[TD]01-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]James[/TD]
[TD]02-03[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
TABLE 1


[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Grade[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00-01[/TD]
[TD]Terry[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01-02[/TD]
[TD]James[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00-01[/TD]
[TD]Terry[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01-02[/TD]
[TD]James[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

TABLE 2

Hey guys,

I'm looking to write a formula in the 'Grade' column in table 1, which returns the grade from Table 2 if the name and date criteria are met.

Thanks!

 

[jtakw]

Hi,

If I understand correctly:

Book1
	A	B	C
1	Grade	Name	Date
2	A	Terry	00-01
3		James	00-01
4		Terry	01-02
5		Bob	01-02
6		James	02-03
7	TABLE 1
8
9
10	Date	Name	Grade
11	00-01	Terry	A
12	01-02	James	C
13	00-01	Terry	B
14	01-02	James	D
15
16	TABLE 2
Sheet154

Cell Formulas
Range	Formula
A2	{=IFERROR(INDEX(C$11:C$14,MATCH(B2&C2,B$11:B$14&A$11:A$14,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

A2 formula copied down to A6
NOTE: Array Formula to be confirmed by CSE, follow instructions above.

 

[danielpeam]

This works and is amazing! Thanks!

Hi,

If I understand correctly:

	A	B	C
Grade	Name	Date
A	Terry	00-01
	James	00-01
	Terry	01-02
	Bob	01-02
	James	02-03
TABLE 1
Date	Name	Grade
00-01	Terry	A
01-02	James	C
00-01	Terry	B
01-02	James	D
TABLE 2

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

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

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

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

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

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

[TD="align: center"]7[/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: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

[TD="align: center"]15[/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]

</tbody>

Sheet154​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]{=IFERROR(INDEX(C$11:C$14,MATCH(B2&C2,B$11:B$14&A$11:A$14,0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



A2 formula copied down to A6
NOTE: Array Formula to be confirmed by CSE, follow instructions above.

 

[jtakw]

You're welcome, glad it works for you.