ASSIGNMENT – 20

INDEX + MATCH — Answer Key

Data Range

  • Salary Range: B2:B21

  • Employee ID Range: D2:D21

  • Search Employee ID: F2:F11

Answers

Q. EMP ID Salary Formula
1 EMP006 ₹63,750 =INDEX($B$2:$B$21,MATCH(F2,$D$2:$D$21,0))
2 EMP014 ₹83,900 =INDEX($B$2:$B$21,MATCH(F3,$D$2:$D$21,0))
3 EMP003 ₹52,800 =INDEX($B$2:$B$21,MATCH(F4,$D$2:$D$21,0))
4 EMP019 ₹70,350 =INDEX($B$2:$B$21,MATCH(F5,$D$2:$D$21,0))
5 EMP011 ₹91,500 =INDEX($B$2:$B$21,MATCH(F6,$D$2:$D$21,0))
6 EMP008 ₹72,400 =INDEX($B$2:$B$21,MATCH(F7,$D$2:$D$21,0))
7 EMP020 ₹93,200 =INDEX($B$2:$B$21,MATCH(F8,$D$2:$D$21,0))
8 EMP005 ₹59,450 =INDEX($B$2:$B$21,MATCH(F9,$D$2:$D$21,0))
9 EMP017 ₹64,800 =INDEX($B$2:$B$21,MATCH(F10,$D$2:$D$21,0))
10 EMP012 ₹78,350 =INDEX($B$2:$B$21,MATCH(F11,$D$2:$D$21,0))

Completed Search Table

EMP ID SALARY
EMP006 ₹63,750
EMP014 ₹83,900
EMP003 ₹52,800
EMP019 ₹70,350
EMP011 ₹91,500
EMP008 ₹72,400
EMP020 ₹93,200
EMP005 ₹59,450
EMP017 ₹64,800
EMP012 ₹78,350

⭐ Best Formula for Students

Enter this formula in G2 if the Search Table has Employee ID in F2 and Salary in G2:

=INDEX($B$2:$B$21,MATCH(F2,$D$2:$D$21,0))

Then drag the formula down from G2 to G11.

 

PreviousNext