Looking up two criteria in a table, one vertical, one horizontal and finding a value

Question

I have a large table of data. Column A is names Columns C-I are monthly commissions. (Column C is March 2023, Column D is April 2023, etc)

Columns C-I are going to change regularly as we are pulling in the last 7 months commissions, and commissions are paid on different dates to different people.

I need to be able to tell google sheets to find the Name in Column A, then look for a match on the month (Which will be a cell reference on that person's individual sheet), and then return that commission.

What I am building is a graph showing on each person's individual goal sheets showing that persons past 6 month commissions. I will be using importrange and initially planned to use Vlookup. But the past 6 months could be different for each person depending on their payday. So I can't make it static, saying pull in column 2, column 3, etc. I'm going to have a list of months for that person and need to pull in the commission for that person that matches that month. I hope that makes sense!

This is where I'm pulling the data for each person.

This is where I need it to go:

Answer 1

Basic Formula

^{Works for a single date/row.}

=INDEX(range, 
   MATCH(name, nameCol, 0),
   MATCH(date, dateRow, 0))

Error Handling

Add the IFNA function to hide errors when no value is found.

=IFNA(INDEX(range, 
   MATCH(name, nameCol, 0),
   MATCH(date, dateRow, 0))) 

Single Formula Solution

=LET(
  commission, BYROW(dates, LAMBDA(r, 
    IFNA(INDEX(range, 
      MATCH(name, nameCol, 0),
      MATCH(r, dateRow, 0))), 0))),
{ commission, 
  SCAN(0, commission, LAMBDA(a,b, a+b)) })

Sample Data

Argument	Reference	Notes
range	A1:I18	Commission, dates, names
name	C21	"Person 7"
nameCol	A1:A18	from range
date	C24	a single lookup date
dates	C24:C30	a range containing multiple rows of lookup dates
dateRow	A1:I1	from range

Formula with Sample Data

=LET(
  commission, BYROW(C24:C30, LAMBDA(r, 
    IFERROR(INDEX(A1:I18, 
      MATCH(C21, A1:A18, 0),
      MATCH(r, A1:I1, 0)), 0))),
{ commission, 
  SCAN(0, commission, LAMBDA(a,b, a+b))})

1. commission stores an array of commissions
   • uses BYROW to pass the date of each month into a LAMDA function
   • The LAMDA's formula uses INDEX to return each month's commission commission from the range of commissions.
     • Column index by matching the date
     • Row index by matching the name (C21 in my example)
2. The result of the formula creates a two-column array (D24:E30 in my example):
   • The first column is the array stored in commission
   • The second column is a new array created by applying SCAN to the commission array which returns the running totals.