Please help me with my IF AND formula including VLOOKUP!!!!

[Lauren Thomas]

Hello

I am new here and in desperate need of some help!

I am wanting to create a formula that says If J7 say "ACTUAL" then do this formula but if it says "FORECAST" then do this formula using Vlookups. I also want to remove the N/A and to generate 0 if nothing is returned.

I think that I should be able to do this using the IF AND function but putting it together is proving difficult.

I think it should be something like this but it needs some tweaking:
If cell J7 says Actual I want it to do the red formula, if it say forecast I want it to do the blue formula. Am I making sense?

=IF((AND(J$7="ACTUAL",J$7="FORECAST")),(ISNA(VLOOKUP($A25&$D$4&J$6&J$7,'FIN TB'!$A$5:$K$29981,8,FALSE)),0,VLOOKUP($A25&$D$4&J$6&J$7,'FIN TB'!$A$5:$K$29981,8,FALSE)),IF(ISNA(VLOOKUP($A41,'FIN BUDGET & FORECASTING'!$A$7:$T$111,6,FALSE)),0,)+VLOOKUP($A41,'FIN BUDGET & FORECASTING'!$A$7:$T$111,6,FALSE)

Best wishes
Lauren

 

[Khalid NGO]

Hello Lauren
Good day and welcome to the forum

Without sample I guess you can go with this:

=IFERROR(IF(J$7="ACTUAL",VLOOKUP($A25&$D$4&J$6&J$7,'FIN TB'!$A$5:$K$29981,8,0),IF(J$7="FORECAST",VLOOKUP($A41,'FIN BUDGET & FORECASTING'!$A$7:$T$111,6,0),0)),0)


If J7 contains only two type of input i.e Actual or Forecast, you can remove the 2nd IF and make it more shorter:

=IFERROR(IF(J$7="ACTUAL",VLOOKUP($A25&$D$4&J$6&J$7,'FIN TB'!$A$5:$K$29981,8,0),VLOOKUP($A41,'FIN BUDGET & FORECASTING'!$A$7:$T$111,6,0)),0)

Regards,

 

[Lauren Thomas]

You just made that so much simpler than I thought it had to be!
You are a superstar genius.

Thank you very much for your help!

 

[Khalid NGO]

Glad it helped.
We appreciate the feedback.

Take Care