2

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. enter image description here

This is where I need it to go: enter image description here

1
  • 1
    Welcome to Web Applications SE. While images are usually helpful, adding the sample data and the formulas that you have tried as text value makes it easier for others to understand the problem and craft and solution. On the other hand, when using ambiguous date formatting, it's important to mention the spreadsheet locale (country) or the date format used. Please add sample data and formulas as text. Commented Sep 22, 2023 at 14:59

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.