Indirect reference of 2 cells in "sheet1" to look up value in table "table1"

mkseto

New Member
Joined
Aug 14, 2018
Messages
42
Office Version
  1. 365
  2. 2013
I have "table1" with dates of 4 months from Nov/2024 to Feb/2025 in the top row starting in A2 (i.e. B2=Nov/1/2024, C2=Nov/2/2024, D2=Nov/3/2024 ... etc.). In the same "table1", the first column lists a bunch of names (A2=John, A3=Dave, A4=Mary .... etc.). The table contains the account balances for each person for each day in the 4 months. For example, the second row lists John's account balances everyday (including weekends) in the 4 months across row #2.
The same workbook has "sheet1" that contains the following amongst all the columns:
"M" = names (e.g. Dave, John, Mary)
"W" = random dates within the 4 months Nov/2024 to Feb/2025 (e.g. Jan/07/2025, Nov/20/2024, Feb/21/2025)
"Z" = whole numbers from 0 to 160

I'm trying to build a complicated formula in column AA for sheet1 to meet the following:
1) Use the name in "M" and date in "W" to find the corresponding value in "table1" (e.g. M2="John" and W2=Nov/05/2024, retrieve account balance for John on Nov/05/2024 in table1 and place in cell AA2)
2) Use the value in "Z" and repeat the above to fill the adjacent "Z less 1" cells. (e.g. using same example above, if Z2=4, then pull the account balance for John for 3 (i.e. 4-1) dates from Nov/6/2024 & Nov/7/2024 & Nov/8/2024 and place the values in cells AB2 & AC2 & AD2). *** in other words, "Z" is the total number of days we need the balances.

I'm hoping to avoid using macro, so I think some type of IF/THEN logic could work to satisfy #2 above by copying the formula from AA to fill the rest of each row so that in the above example of Z2=4, the formula will retrieve values to place in AA2 to AD2 and then with "N/A" beyond AD2.

After trying for weeks with no luck, I have come to realize this is well beyond my limited Excel skills, so I'm hoping someone can help me accomplish this.
 
AA2 and copy right:
Excel Formula:
=IF(COLUMNS($AA$1:AA$1)<=$Z2,INDEX(table1!$B$2:$DR$30,MATCH($M2,table1!$A$2:$A$30,0),MATCH($W2-1+COLUMNS($AA$1:AA$1),table1!$B$1:$DR$1,0)),"")

Could shorter and more important - a spill formula if you use Excel version 365 (2021 shall probably do the work too, so my suggestion is - edit your profile and add excel version info).
Sheet table1:
Book1
ABCDEFGHIJ
110.02.202511.02.202512.02.202513.02.202514.02.202515.02.202516.02.202517.02.202518.02.2025
2John123456789
3Amy151413121110987
4Ella67891011121314
table1


Sheet Sheet1
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1
2Amy15.02.202531098  
3
Sheet1
Cell Formulas
RangeFormula
AA2:AE2AA2=IF(COLUMNS($AA$1:AA$1)<=$Z2,INDEX(table1!$B$2:$DR$30,MATCH($M2,table1!$A$2:$A$30,0),MATCH($W2-1+COLUMNS($AA$1:AA$1),table1!$B$1:$DR$1,0)),"")
 
Upvote 0
Solution
Just in case a spill formula (so no copying to the right needed) proposition could be:
Excel Formula:
=LET(x,table1!$A$1:$DR$30,z,MATCH($M2,CHOOSECOLS(x,1),0),y,MATCH($W2,CHOOSEROWS(x,1),0),IFERROR(CHOOSECOLS(CHOOSEROWS(x,z),SEQUENCE(1,$Z2,y)),""))

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1
2Amy15.02.202531098
3
Sheet1
Cell Formulas
RangeFormula
AA2:AC2AA2=LET(x,table1!$A$1:$DR$30,z,MATCH($M2,CHOOSECOLS(x,1),0),y,MATCH($W2,CHOOSEROWS(x,1),0),IFERROR(CHOOSECOLS(CHOOSEROWS(x,z),SEQUENCE(1,$Z2,y)),""))
Dynamic array formulas.
 
Upvote 0
Just in case a spill formula (so no copying to the right needed) proposition could be:
Excel Formula:
=LET(x,table1!$A$1:$DR$30,z,MATCH($M2,CHOOSECOLS(x,1),0),y,MATCH($W2,CHOOSEROWS(x,1),0),IFERROR(CHOOSECOLS(CHOOSEROWS(x,z),SEQUENCE(1,$Z2,y)),""))
Wow, you are super FAST, I was still trying to google various functions and thought there's already a response!
I copied the different formula above but somehow getting "#N/A" in the cells I was expecting values (some other cells with "0" in column "Z" returns blanks, which is good). I'm sure I must have done something wrong so let me double check. By the way, after I copied the formula, I get a popup "Update values: table1" window and I just close it ... is that expected and am I supposed to select a file?
Again, appreciate the quick response!!!
 
Upvote 0
In the formulas instead of table1 use a sheet name, where table 1 is located.
You have not mentioned it.
You have only said the name of the "output sheet": >>The same workbook has "sheet1"<<
 
Upvote 0
In the formulas instead of table1 use a sheet name, where table 1 is located.
You have not mentioned it.
You have only said the name of the "output sheet": >>The same workbook has "sheet1"<<
My apology for not being clear in my original post (and I noticed some typo's as well :(
Your first solution definitely works .... I'm using Excel 2013, therefore couldn't try the other formula :(
Thanks a million for the quick/super help.
I will now need to figure out why when I apply the same formula to another spreadsheet but didn't work (large file, fields are slightly different but same structure, but getting the #n/a error. Anyway, I'm sure I can get your formula to work on that sheet).
Thanks again!!!!!
 
Upvote 0
Glad we could help.

As for #NA in other workbook: For me first thing to be checked is: whether all dates are real Excel dates (both in source table and in W2. Easy test - change format of a cell containing a date to number. If the value display now as 5digits number, like 45705.00 or similar, it is proper dates. If not - it is probably just texts looking as a date. Second point - names. They have to match exactly. So >John< is not the same as >John < (with space ar the end) and John Black is different than John Black where a space in one case is just a standard space and in the other a non-breaking space (ASCII character 160). etc.
 
Upvote 0
Glad we could help.

As for #NA in other workbook: For me first thing to be checked is: whether all dates are real Excel dates (both in source table and in W2. Easy test - change format of a cell containing a date to number. If the value display now as 5digits number, like 45705.00 or similar, it is proper dates. If not - it is probably just texts looking as a date. Second point - names. They have to match exactly. So >John< is not the same as >John < (with space ar the end) and John Black is different than John Black where a space in one case is just a standard space and in the other a non-breaking space (ASCII character 160). etc.
I finally got it fixed: I tested the first "MATCH" statement and it yielded the match results, so that was fine. Testing the second "MATCH" yielded the #N/A error, so I knew that was what I had to look into. Long story short, the date field was really messy in both "table1" and "sheet1" (source data was manually entered). It was easy enough to clean up table1 since date were sequential, it was much more challenging for "sheet1" :(
I really need to THANK YOU yet another time, there's no way I could come up with this formula (and I had googled it for weeks). Once more thing, I was able to find a PC with Excel 365 to test the other spill formula: it worked perfectly!!!
Thank you again, you ROCK!!!
 
Upvote 0
You made my evening with these comments. :love:

And what I appreciate most is that you use my help as a kind of mentoring or "showing a direction" and do the details by yourself.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top