Vlookup with dates for investment IDs in Numbers

Question

Level 1

4 points

I have two tables for my investments. The first one has the Col(A) is Invest I.D. Col(B) has Mature Date

The second is a table laid out as a calendar. Col(A) is Months and Col(B) has Year {2025} (C) is {2026} etc.

I would like to populate the calendar with the investment ID's in the correct month and year relative to the Mature date of each investments

Invest ID. 2025. 2026. 2027 etc.

Jan a

Feb c

Mar. b

Apr d

The investments table changes both ID and mature date. The formulas in calendar table must show the changes made on investment table

I've am having a great deal of trouble using Vlookup with dates. I appreciate any help you can give me.

Thanks in advance

[Re-Titled by Moderator]

Original Title: Numbers - Vlookup

iMac 24″, macOS 15.6

Posted on Sep 4, 2025 9:50 PM

Reply

Answer 1

Top-ranking reply

Level 8

37,532 points

And this will work even if maturities are not on the first of the month:

=XLOOKUP(DATE(B$1,MONTH($A2),1),DATE(YEAR(Table 1::$B),MONTH(Table 1::$B),1),Table 1::$A,"")

SG

Reply

Answer 2

Level 8

37,532 points

XLOOKUP is a more modern version of VLOOKUP. If you can assuming your maturity dates are the 1st of the month, then you can do something like this:

In B2, filled right and down:

IFERROR(XLOOKUP(DATE(B$1,MONTH($A2),1),Table 1::$B,Table 1::$A),"")

Use ; instead of , in the formula if your region uses , as the decimal separator.

Edit. Actually, this formula is simpler:

=XLOOKUP(DATE(B$1,MONTH($A2),1),Table 1::$B,Table 1::$A,"")

SG

Reply