Is it possible to IF, VLOOKUP else SUM?

Jeevz_87

New Member
Joined
Sep 21, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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???

Thank you in advance!


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
Joined
Jun 12, 2014
Messages
81,661
Office Version
  1. 365
Platform
  1. Windows
What exactly do you mean by "SUM the exist calculation instead."?
 
Upvote 0

Jeevz_87

New Member
Joined
Sep 21, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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)
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,661
Office Version
  1. 365
Platform
  1. Windows
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)))
 
Upvote 0

Jeevz_87

New Member
Joined
Sep 21, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Thanks Fluff, just giving it a test and it returns FALSE the moment that the dates change (G4), would this require an IFERROR?
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,661
Office Version
  1. 365
Platform
  1. Windows
What should happen if neither of the criteria are true?
 
Upvote 0

Jeevz_87

New Member
Joined
Sep 21, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
In that case, revert to summing the existing calculation I would say.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,661
Office Version
  1. 365
Platform
  1. Windows
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))
 
Upvote 0
Solution

Jeevz_87

New Member
Joined
Sep 21, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,661
Office Version
  1. 365
Platform
  1. Windows
What should happen if BP10 is not greater than 0?
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top