Excel nested month formula

[DeepuSultan]

I have this formula for two months, and need to extend it for 12 months. It's already long, how to shorten it for 12 months?

The values are stored in separate tabs e.g. "Jan 24 Rate"

=IF(D1="","N/A",IF(MONTH(D1)=1,IF(G1="USD",O1,O1/VLOOKUP(G1,'Jan 24 Rate'!$B$2:$C$128,2,0)),IF(MONTH(D1)=2,IF(G1="USD",O1,O1/VLOOKUP(G1,'Feb 24 Rate'!$B$2:$C$128,2,0)))))

 

[Phuoc]

Try this for 12 months:

=IF(D1="","N/A",IF(G1="USD",O1,O1/VLOOKUP(G1,INDIRECT("'"&TEXT(D1,"mmm")&" 24 Rate'!$B$2:$C$128"),2,0)))

 

[DeepuSultan]

Try this for 12 months:

=IF(D1="","N/A",IF(G1="USD",O1,O1/VLOOKUP(G1,INDIRECT("'"&TEXT(D1,"mmm")&" 24 Rate'!$B$2:$C$128"),2,0)))

Note that the valus will be in multiple tabs and I need to pull values from it. These are the tab names Jan 24 Rate, Feb 24 Rate and so on. I will add the sheets till Dec and need to pull values from each sheet based on the number of the month.

 

[DeepuSultan]

Note that the valus will be in multiple tabs and I need to pull values from it. These are the tab names Jan 24 Rate, Feb 24 Rate and so on. I will add the sheets till Dec and need to pull values from each sheet based on the number of the month.

How to incorporate these in your formula?

 

[Fluff]

The formula Phuoc posted does what you want. Did you try it?

 

[DeepuSultan]

The formula Phuoc posted does what you want. Did you try it?

It is working. Thank you.