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

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:

• 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.
– Rubén
Sep 22 at 14:59

## 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.