Find value of a cell according to month and year

Admin 1
Viewed 100

I’m sorry in advance if I’m not clear for you.

I have for example :

Figure 1 :

| A        |       B  |
|01/05/2019|     500  |
|01/07/2020|  -100000 |
|01/12/2020|    7633  |

And

Figure 2 :

|     A    |   B       |
|01/05/2019|  -0.3521  |
|01/07/2020|    0.455  |
|01/12/2020|       0   |

I would like to include in the formulas in column B of Figure 1, the value associated with the month and year in relation to column A in figure 2 (found with the date in column A figure 1), bearing in mind that the days are different.

Thank you.

datetime-format excel-formula vba excel datetime
1 Answers

I would advise you to use a helper column, containing unique information on the month and the year. Question is, how will you do this? I propose following formula:

Helper(date) = 12*Year(date) + Month(date)

Where Year() and Month() are the basic Excel function for retrieving the year number and the month number of the date. Using this approach, you can then use the VLookup() function for finding the column you are looking for, something like:

Figure1:
========

Helper |          A |       B  |
 24233 | 01/05/2019 |     500  |
 24247 | 01/07/2020 |  -100000 |
 24252 | 01/12/2020 |    7633  |

Figure2: (with some modification, I used the second day of the month to clarify 
========  the correctness of the provided formula)

Helper |          A |       B  |
 24233 | 02/05/2019 |  -0.3521 |
 24247 | 02/07/2020 |    0.455 |
 24252 | 02/12/2020 |       0  |

As you can see, a simple VLookup() can be used for searching for the values 24233, 24247, 24252, ...

user_42791550
Related