Explanation

At a high level, we use VLOOKUP to extract employee information in 4 columns with ID as the lookup value:

=VLOOKUP($I$4,Table1,MATCH(H5,Table1[#Headers],0),0)
  • The ID value comes from cell I4, and is locked so that it won’t change as the formula is copied down the column.
  • The table array is Table1, with data in the range B5:F104.
  • The column index is provided by the MATCH function.
  • The match type is zero, so force VLOOKUP to perform an exact match.

The MATCH function is used to get a column index for VLOOKUP like this:

MATCH(H5,Table1[#Headers],0)

Values in column H correspond to the headers in the table, so these go into MATCH as lookup values:

  • The lookup value comes from cell H5
  • The array is the headers in Table1, specified as a structured reference.
  • The match type is set to zero to force an exact match.

This is what accomplishes the two-way match. MATCH then returns the position of the match. For the formula in I5, the position is 2, since “First” is the second column in the table. VLOOKUP then returns the first name for id 601, which is Adrian.

Note: VLOOKUP depends on the lookup value being to the left of the value being retrieved in a table. Generally, this means the lookup value will be the first value in the table. If you have data where the lookup value is not the first column, you can switch to INDEX and MATCH for more flexibility.

Explanation

The SUMIFS function is designed to sum numeric values using multiple criteria. In the example shown, the data in the range B5:E15 shows a sales pipeline where each row is an opportunity owned by a salesperson, at a specific stage. The formula in H5 is:

=SUMIFS(value,name,$G5,stage,H$4)

The first part of the formula sums opportunities by salesperson:

=SUMIFS(value,name,$G5 // sum by name
  • Sum range is the named range values
  • Criteria range 1 is the named range name
  • Criteria 1 comes from cell G5

Notice $G5 is a mixed reference , with the column locked and the row relative. This allows the formula to change as needed when the formula is copied throughout the table.

The next range/criteria pair in SUMIFS, sums by stage:

stage,H$4 // sum by stage
  • Criteria range 2 is the named range stage
  • Criteria 2 is H$4

Again, H$4 is a mixed reference, with the column relative and the row locked. This allows the criteria to pick up the stage values in row 4 as the formula is copied across and down the table.

With both criteria together, the SUMIFS function correctly sums the opportunities by name and by stage.

Without names ranges

This example uses named ranges for convenience only. Without named ranges, the equivalent formula is:

=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)

Notice references for name, value, and stage are now absolute references to prevent changes as the formula is copied across and down the table.

Note: a pivot table would also be an excellent way to solve this problem.