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

 

[Fluff]

What exactly do you mean by "SUM the exist calculation instead."?

 

[Jeevz_87]

Hi Fluff,


I knew I missed something - apologies.

The existing calculation is the following which is bespoke to this sheet;

=IFERROR((($DA10+SUM($AD10:$AD10))/$Y10)*$AA10-$DJ10,0)

 

[Fluff]

Ok, maybe

=IF(AND(J10= "Yes",BR$7=$G$4),XLOOKUP(G10,Feeder!D8:D2000,Feeder!BV8:BV2000),IF(AND(J10= "No",BR$7=$G$4),IFERROR((($DA10+SUM($AD10:$AD10))/$Y10)*$AA10-$DJ10,0)))

 

[Jeevz_87]

Thanks Fluff, just giving it a test and it returns FALSE the moment that the dates change (G4), would this require an IFERROR?

 

[Fluff]

What should happen if neither of the criteria are true?

 

[Jeevz_87]

In that case, revert to summing the existing calculation I would say.

 

[Fluff]

In that case try

=IF(AND(J10= "Yes",BR$7=$G$4),XLOOKUP(G10,Feeder!D8:D2000,Feeder!BV8:BV2000),IFERROR((($DA10+SUM($AD10:$AD10))/$Y10)*$AA10-$DJ10,0))

 

[Jeevz_87]

Hi Fluff,

Thanks for the solution as it worked out pretty well however the requirements shifted and I ended up changing the formula to this;

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

However, it keeps returning a value of FALSE instead of a number. Is there anyway i can amend this to return a 0 instead?

Many thanks,


Jeevz

 

[Fluff]

What should happen if BP10 is not greater than 0?