Index Match question.... trying to use single value instead of array for index part

[RJ_CCI]

The top image is the driver page which has the lease number, the payment amount, and the dates that every payment gets made (monthly). I am trying to make a payment calendar where it shows for every lease the payment that day if there was one. I got it to work by doing an index match, but in order to do that I had to insert a row on the driver page underneath every lease with a payment for the corresponding date, in which case i just absolute referenced pmt amount and dragged it across. But i am sure there is a way that I can do this where I don't need to do that. I tried to do an index match where in the first part it asks for an array , i just absolute referenced the payment amount (not an array like the function calls for), and then matched the date row on the driver page to the singular date on the calendar, and it works for the first payment but then says #ref for every date the payment is supposed to hit after. Again, i bypassed this issue by putting an array of payments below every lease by I don't want to have to do that so I can just drag all the way down.

Thank you to anybody and everybody for all efforts in helping address this question!

Ryan

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what are you expecting to see in the bottom image?

 

[RJ_CCI]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what are you expecting to see in the bottom image?

I am using version 2108.

In the bottom image I want to see the payment amount from the driver (top) page for each corresponding lease in the payment dates listed on the driver page. For example, for lease 001, i want it to pull the payment amount for that lease ($1600) and on the payment dates listed on the top image to the matching date in the bottom image. Lease 001 pays on the 28th of each month, so on 9/28 and 10/28 etc on the bottom the $1600 pmt would show up on that day

 

[Fluff]

I am using version 2108.

There is no such version I suspect that is the build number & that you are on O365. Please update you account accordingly.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5
6
7
8	001	1		10/09/2021	13/09/2021	17/09/2021
9	002	2		11/09/2021	15/09/2021
10	003	3		12/09/2021
11	004	4		08/09/2021	10/09/2021	14/09/2021
12	005	5
13
14
15		08/09/2021	09/09/2021	10/09/2021	11/09/2021	12/09/2021	13/09/2021	14/09/2021	15/09/2021	16/09/2021	17/09/2021	18/09/2021	19/09/2021	20/09/2021	21/09/2021
16	001			1			1				1
17	002				2				2
18	003					3
19	004	4		4				4
20	005
Sheet3

Cell Formulas
Range	Formula
B16:O20	B16	=IFERROR(INDEX($B$8:$B$12,AGGREGATE(15,6,(ROW($B$8:$B$12)-ROW($B$8)+1)/($A$8:$A$12=$A16)/($D$8:$X$12=B$15),1)),"")

 

[RJ_CCI]

that certainly works but seems overly and unnecessarily complex. feel like there is a simpler way to do it. I am just trying to do an index match and the match isn't the problem, it is pulling the date just fine, it just is forcing me to use an array rather being able to plug in one number

 

[Fluff]

What is your formula?

 

[RJ_CCI]

See below. option 1 is the one I am trying to get to work. I know all i need to do is use iferror with "" at the end to make it return only the value if there is one, but leaving it there to illustrate the issue. You see where the payment is supposed to hit on the Calendar, it works for the first payment on 1/2 but says #ref where the other two payments are supposed to hit on 1/5 and 1/8, and I presume it is because I am not listing an "array" in the first requirement of the index function like it asks for. I can get it to work in option 2 because I provide a matching array of payments underneath the dates I want the payments to hit, in which I just absolute reference the payment cell, and you can see it returns the payment on the calendar for all the payment dates. I ideally don't want to have to enter in another row with the payments underneath all of the payment dates on the driver page because then it makes it so I can no longer just drag down the formula on the calendar for all the leases (there is 100+ of them). Ideally it can just use the absolute reference of the pmt amount instead of an array. (I also tried to do $B$7:$B$7 instead of just B$7$ and it didn't work either). Let me know if my question/issue now makes more sense. Thank you for baring with me here.

 

[Fluff]

That layout is totally different to what you showed originally.
If you are not interested in matching the Lease number then you can use

=IF(ISNA(MATCH(B11,$C$7:$E$7,0)),"",$B$7)

 

[RJ_CCI]

aha! perfect! I don't need to function it to match the lease number, I just set it to the first lease and then drag down and it will automatically match. this works perfect. thanks