How to write dynamic array references inside a XLOOKUP and FILTER functions

Rafael Morales 0

Dear community, I will appreciate your help, advice and support with the following:

I have a master file with all of the residents contributions for maintenance and repairing on our house group.

From this master file, I'm looking up for each resident contribution on each month and year. My lookup and filter functions are working properly, but I should write the same functions over and over for each month and year combination. I would like to use Month cell value as well as Year one inside the function and make it dynamic.

I have already searched and tries with INDIRECT function, but I get a #REF error.

I will thank if you propose me a solution.

I'm attaching an image to be clearer on my issue.

Thanks in advance.

Screenshot 2024-04-15 at 13.42.07

Barry Schwarz 2,111 Reputation points

2024-04-15T21:53:01.21+00:00

We could probably offer more substantial help if you gave us a readable example of the worksheet and a sample of the selection process you are trying to perform. I also think you would be better off using a macro function rather than trying to fit this into a single-cell formula.

Does example 3 of webpage "https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60676&ui=en-us&rs=en-us&ad=us" help at all?
Rafael Morales 0 Reputation points

2024-04-15T23:54:48.49+00:00

Estrellas_test_2.pdf
Rafael Morales 0 Reputation points

2024-04-15T23:55:59.0133333+00:00

Barry,

I'm sending you PDFs from both files. I could not send the Excel files. Thanks.
Rafael Morales 0 Reputation points

2024-04-15T23:56:32.0733333+00:00

Ingresos_Egresos 2024_Abril_Short_2.pdf
Barry Schwarz 2,111 Reputation points

2024-04-16T05:09:52.24+00:00
Is the following understanding correct?

You have a separate worksheet for each month/year containing all the deposits from all the homes. This is your database from which you want to extract data.

You also have a separate worksheet for each home which contains the extracted data for that home from the deposits worksheets. Each year occupies four data rows and twelve month columns. For a particular month and year:

The first row (C16 in the sample) is the date of the first deposit. The formula seems correct but workbook and worksheet names confused me.

The second row (C17) is the sum of the deposits. It appears to work but I believe the SUMIF function would be much simpler - SUMIF([book]sheet!C8:C26,A3,[book]sheet!D8:D26) It would be even better if you gave the array names instead of having to repeat the book and sheet names.

The third row (C18) is a count of the deposits. You did not show the formula but the COUNTIF function would work similar to the SUMIF.

The fourth row contains what?

What do you want to do?

C44 is supposed to find a deposit from this home. Since this is in the January column, is it to search for a deposit in any January? Or for any deposit in 2023? Or for any deposit at anytime. When it finds this deposit, what should be placed in cell C44? The date? The amount?

Cell C45 is to sum the deposits in a particular month and year. That sum is already available (as in cell C17).

I don't know what you mean by convert on stars for cell C46
Rafael Morales 0 Reputation points

2024-04-16T16:59:14.6333333+00:00

Hi Barry,

Thanks for your interest. Sorry about file and worksheet names, but it will be handled by major persons with limited experience on Excel.

You’re right. One file contains separate worksheets for each month along the year. Each of those are uploaded on a private website to keep an eye on income and expenses.

Cell C16 checks the first date of a deposit made by this home in particular. If there is no deposit it will show a red text signaling the lack of the payment.

C17 sums the total amount of deposits made by the home, since some neighbors made more than one in the month.

C18 shows the total amount of deposits made on this particular month. It has a COUNTIF function. This and the fourth row come from and old idea from the neighbors to show a star for every deposit. Those would eventually dissapear.

C44,C45 and C46 are only copies from the the above mentioned cells to show you the functions, those won’t be appear on the final report.

My intention is to take the value of the column header (month) and row header (year) to make the LOOKUP function to find the right data on the external file at the worksheet for this particular month/year combination.

I don’t know how to write a dynamic reference inside this function, instead to be writing the specific month and year.

I will appreciate your comments back.

Thanks.

1 answer

Rafael Morales 0 Reputation points

2024-04-15T23:41:34.81+00:00
Barry,

In first instance, thanks for your interest to help me.

I'm struggling to attach a short version of both files. One is 20k another 800k, but none is allowed to me to upload. Please allow me to figure out how to send them to you.

Thanks.
Please sign in to rate this answer.
Jiajing Hua-MFST 4,905 Reputation points Microsoft Vendor

2024-04-16T06:46:50.3633333+00:00

Hi @Rafael Morales

You may save them as ,xml files, then upload to Q&A.

But please note, clear any privacy information. Thanks ~

Rafael Morales 0 Reputation points

2024-04-16T17:40:05.55+00:00

Dear Jiajing Hua,

Thanks a lot for your advice. I will take out any private information.

Keep well. Thank you.
Sign in to comment