Placeholder Image

Subtitles section Play video

  • right.

  • We've seen several function combinations so far in this lesson.

  • Well present another one that can be useful.

  • I suppose you'd like to create a table with the number of people working in management and admin personnel for company D and E.

  • Again, we can use match to define the column number we need in the V look of function.

  • I am sure you now know the first part of the V.

  • Look up, function by heart.

  • This will be our look of value and we will fix it's column reference.

  • The entire table above is the look up array.

  • Cool.

  • This is when match comes in play.

  • The look of value of match will be this cell when I copy man paste this function.

  • I need this reference to change on Lee when I move to the right along the horizontal axis and not to change when I moved down.

  • Therefore I will anchor It's row reference Great.

  • In the next step, we have to define the area or the look up array where Excel will try to find the look of value.

  • In our case, that means company D.

  • Therefore, I will select the header of the source table and fix it.

  • It is not supposed to move when we copy and paste the function, and this pretty much does it all right.

  • It worked okay for the number of managers in Company D, let's apply this formula to the other three cells in the table.

  • It worked.

  • Therefore, one could combine V, look up and match in the same formula from all that you've learned.

  • Until this moment, you would probably agree that nesting a match function within a V look up differs from nesting columns within a V.

  • Look up, though.

  • Now, if I replace the name of a company with another one, the number of workers in the management and admin personnel would change accordingly.

  • This is not the case with the columns function as it works fine on Lee for adjacent companies in the source table.

right.

Subtitles and vocabulary

Click the word to look it up Click the word to find further inforamtion about it