Nested VLOOKUP, or similar

Hi. I need some help to collate data for my dissertation. I have a huge dataset of dividend returns, ordered by stock and date paid. They are essentially a "column vector of spaced arrays" where each array/table is dividend date vs dividend payment, for each stock in the dataset (hope that makes sense). This was the only output i could get.

I need to rearrange this into a cross-sectional table, with stocks vs 20 years of daily dates. The result I want to achieve is to export the value of the dividend payment IF the date of the dividend payment equals the relevant date in the output sheet. ie, it will check the relevant cell of the 20 years of daily data. So usually no dividend output, but occasionally a value would be included.

The part that I need help with is this: how can I get that dividend value on a stock by stock basis? So the coding would need to first determine if the dividend table at hand is actually related to the specific stock being sort, so that they match up and are not just randomly pulling in results from other stocks. I expect it is some type of nested VLOOKUP, but I don't know how to do it...

Massive thanks to anyone who can shed light on this and save me from my supervisor!

PS - I have attached a spreadsheet showing the dividend data and the output area as described above.

Here is one option.
1. Copy and paste down the formulas in D,E,F and G.
2. Check the Errors in column G, by filtering the column for value True (there are Input Errors !).
3. Then update the Pivottable.

HTH
Ola

Hi, and welcome to the forum.

Add two new helper columns A & B to your Dividend Data sheet.

Now add the following formulae and copy down the whole of columns A & B

Please Login or Register  to view this content.

Now on the dividend value sheet enter in B3

Please Login or Register  to view this content.

And copy this down and across as necessary. Be prepared for a lot of recalculation time if you have thousands of rows and columns

HTH

Thanks both for your help. I have used your approach Richard and it has done EXACTLY what I was after! Absolutely awesome. No way I would have got out that coding by myself...
It's true that it is a fairly lengthy process with the calculating of the code on a lot of data, but simply being able to get the results by stock and date is brilliant. Thanks again, you guys rock.

Hi,

Thanks for the feedback. Nice to know we could help.

Remember that intermediate helper columns are often extremely useful. I've lost count of the number of times these have helped me solve a problem.

Regards