# Is it possible to IF, VLOOKUP else SUM?

#### Jeevz_87

##### New Member
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;

Excel Formula:
``=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???

Jeevz

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Fluff

##### MrExcel MVP, Moderator
What exactly do you mean by "SUM the exist calculation instead."?

#### Jeevz_87

##### New Member
Hi Fluff,

I knew I missed something - apologies.

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

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

#### Fluff

##### MrExcel MVP, Moderator
Ok, maybe
Excel Formula:
``=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

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

#### Fluff

##### MrExcel MVP, Moderator
What should happen if neither of the criteria are true?

#### Jeevz_87

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

#### Fluff

##### MrExcel MVP, Moderator
In that case try
Excel Formula:
``=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

##### New Member
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;

Excel Formula:
``=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

##### MrExcel MVP, Moderator
What should happen if BP10 is not greater than 0?

Replies
0
Views
124
Replies
2
Views
198
Replies
7
Views
320
Replies
2
Views
354
Replies
5
Views
204

1,186,799
Messages
5,959,838
Members
438,452
Latest member
Poor Dave

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back