Question

Problem 1: LA County Health Department - Revenues/expenses update (VLOOKUP and IF combined) LA County Health Department (LACHD) supervises private healthcare providers that undertake government projects. The LACHD would make estimates of the cumulative revenues/expenses of the providers on monthly basis, and on the tenth day after each quarter ends, update its estimated data for the quarter with the new data provided by the providers The rules of the update:(1) If the updated data from a provider matches LACHD's estimate, the amount stays as the estimated value; (2) if there is no report from a provider, the amount stays as the estimated value; (3) if the estimated value deviates from the reported actual value, the reported value is used to update (replace) the estimated value. [Based your formula on these rules]

Answer 1

The formula in cell C3 should be =IF($B2=VLOOKUP($A2,$D$2:$E$17,2,0),$B2,IF(VLOOKUP($A2,$D$2:$E$17,2,0)=0,$B2,VLOOKUP($A2,$D$2:$E$17,2,0)))

The drag it till C18.

See the screenshot below, I have not typed all the data but this should be helpful

/*If this helps you, please let me know by giving a positive thumbs up. In case you have any queries, do let me know. I will revert back to you. Thank you!!*/