Return a specific cell if two criteria is met

[danielpeam]

Grade	Name	Date
	Terry	00-01
	James	00-01
	Terry	01-02
	Bob	01-02
	James	02-03

<tbody>
</tbody>

TABLE 1

Date	Name	Grade
00-01	Terry	A
01-02	James	C
00-01	Terry	B
01-02	James	D

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet154​

Array Formulas Cell Formula A2 {=IFERROR(INDEX(C$11:C$14,MATCH(B2&C2,B$11:B$14&A$11:A$14,0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.