Vlookup formula does not work for some cell

Excel&VBA · Nov 29th 2020

I have been trying to correct the mistake but i am unable to understand the issue that, it is working for all cell instead of 12 that are highlited.

can someone please make it correct or share an accurate formula to use.

any help will be highly appreciated.

Pecoflyer · Nov 29th 2020

For example D8 boils down to searching the "H2-2020" string which does not exist in the first column in your range $A$2:$B$61,2,0)

Same thing for the strings searched by the other N/A results

Excel&VBA · Nov 29th 2020

Sir exactly

Formula is not working on D8:D19 because there is no H1-2021 value in Col "A".

If such condition is happen then formula should consider Q1-2021 and Q2-2021 as H1-2021 for accurate calculation as "D20" has.

bosco_yip · Nov 30th 2020

In D8 formula copied down :

=(1+IFERROR(VLOOKUP(VLOOKUP(MONTH(C8),$H$8:$I$19,2,0) & "-" & YEAR(C8),$A$2:$B$61,2,0),B$2))^(1/12)

Regards

Excel&VBA · Nov 30th 2020

Sir, thank you so much for the help.

Participate now!