Is it possible to IF, VLOOKUP else SUM?

[Jeevz_87]

Hi All,


I'm trying to overwrite an existing calculation if the data from another sheet is available, else carry out the existing calculation. The problem is I cant seem to get the formulae logic right.

What I would like is the following;

1) IF cell J10 in Sheet A = "Yes" AND date in BR7 = $G$4, then VLOOKUP using the project name in Sheet A in G10 against the projects in Sheet B and return the corresponding value from the Margin column.

2) IF cell J10 in Sheet A = "No" AND date in BR7 = $G$4, then SUM the exist calculation instead.

I've managed to do the first part correctly using dynamic headings which looks like this;

=IF(AND(J10= "Yes",BR$7=$G$4),XLOOKUP(G10,Feeder!D8:D2000,Feeder!BV8:BV2000))

But, I cant seem to do point 2).

How do I incorporate this into the above formulae???

Thank you in advance!


Jeevz

 

[Jeevz_87]

Good question, it should then just execute (($EM10+SUM($AC10:AD10))/$X10)*$Z10-($EV10+SUM($CZ10:CZ10)) which is a bespoke formulae that the client wanted. If the value in BP is greater then 0 then return the value in BP.

I hope that makes sense

 

[Fluff]

In that case just add that formula to the False part of your previous formula.

 

[Jeevz_87]

Apologies for sounding abit dense (I think ive been staring at this too long), how would that amended formula look?

 

[Fluff]

Try

=IF($BP10>0,BP10,IFERROR((($EM10+SUM($AC10:AD10))/$X10)*$Z10-($EV10+SUM($CZ10:CZ10)),BP10))

 

[Jeevz_87]

Hi Fluff,

Sorry about the wait. So, if BP10 is not greater than 0 then the formula should execute the long sum formula to the right. I've actually managed to fix the issue by using the following;

 =IF($BP10>0,$BP10,IF($BP10="",SUM($EM10+SUM($AC10:AD10)/$X10)*$Z10-($EV10+SUM($CZ10:CZ10)),0))

Thanks again for your help with this Fluff, wouldn't have been able to progress this without your help

Jeevz

 

[Fluff]

Glad you sorted it & thanks for letting us know.