CODEWITHSUNDEEP
excel
user3333198
user3333198

Reputation: 105

INDEX MATCH with different sizes of arrays

I am trying to link dividends of several stocks to a range of dates. The dividends are several months apart, where column A is the date and column B is the amount of the dividend. Each stock has about 10-20 dividend payments in the last 5 years. I am now trying to distribute these dividend payouts across a daily timeline, where column A shows daily dates from today until 2005 (3818 rows).

I tried to do this with INDEX/MATCH with the following formula:

=INDEX([A68U.SI.csv]A68U.SI!$B$2:$B$13,MATCH([A68U.SI.csv]A68U.SI!$A2,$A2:$A3818))

However, I get a #REF error. This seems to be the wrong approach. Does anyone have an idea? Many thanks!

Upvotes: 1

Views: 1888

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

Use VLOOKUP:

=VLOOKUP(A2,[A68U.SI.csv]A68U.SI!$A$2:$B$13,2,FALSE)

To remove errors for dates when there are no paid dividends:

=IFERROR(VLOOKUP(A2,[A68U.SI.csv]A68U.SI!$A$2:$B$13,2,FALSE),"")

If you have more than one paid dividend on the same date, you will have to use something different.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46341

I think you need to switch that around, e.g. in B2 copied down

=LOOKUP(A2,[A68U.SI.csv]A68U.SI!$A$2:$B$13)

That will lookup the A2 date in Your payment table and find the relevant dividend for that date.

Upvotes: 0

Related Questions