ASSIGNMENT – 19

Use of Formula – INDEX with MATCH

Monthly Sales Report

Enter the following data in Excel:

REGION JAN FEB MAR APR
North 6,250 5,480 9,150 7,820
South 5,750 6,120 11,450 8,960
East 7,200 4,350 6,750 9,540
West 3,850 4,620 7,480 6,930
Central 8,150 7,250 10,200 9,850

Questions

Use INDEX + MATCH to find the following sales values:

  1. Find the March Sales of East.

  2. Find the February Sales of West.

  3. Find the January Sales of Central.

  4. Find the April Sales of North.

  5. Find the March Sales of South.

  6. Find the April Sales of East.

  7. Find the January Sales of West.

  8. Find the February Sales of Central.

  9. Find the March Sales of North.

  10. Find the April Sales of Central.

Formula Pattern

To find East – March, use:

=INDEX(B2:E6,MATCH("East",A2:A6,0),MATCH("Mar",B1:E1,0))

Formula Structure

=INDEX(data_range,MATCH(region,region_range,0),MATCH(month,month_range,0))

Important

  • INDEX returns the value from the selected row and column.

  • The first MATCH finds the Region/Row.

  • The second MATCH finds the Month/Column.

  • 0 means Exact Match.

  • Use the same INDEX + MATCH method for all 10 questions.

PreviousNext