IF Error/Vlookup Help

excelexcelexcel123

New Member
Joined
Jul 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I am trying to use a vlookup to calculate averages across multiple sheets. I think I am close to getting the formula right, my only issue is records in Column A on my summary sheet are not returning a value if the record is not on both sheets. Cell B5 for example. What am I missing here? Thanks in advance.
 

Attachments

  • 2021-07-01 16_12_28-Excel.png
    2021-07-01 16_12_28-Excel.png
    34.9 KB · Views: 18

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Your formula has a lookup to Jun 23 and one to Jun 24. If either lookup can't find the value then it that look up will return #N/A.
The Average will return #N/A if either of the 2 lookups returns #N/A.
The IfError will then take over and return ""

You need to decide:
  • Is the average calculation valid if either value is not found ?
    If not then returning blank is fine.
  • If you do consider it is still valid what calculation do you want to use if
    - Jun 23 is not found
    - Jun 24 is not found
    eg if you just wanted to set the not found one to 0
    =average(Iferror(vlookup(A3,Jun23,6,0),0),iferror(A3,Jun24,6,0))
  • Note: If you wanted to have different combinations you might need to fall back to using If(ISNA(expression),true,false) or if (ISERROR(expresssion),true,false)
 
Upvote 0
Thanks for the response. To provide a little more detail, I am trying to rates of a certain outcome of phone calls. The value in B5 on the summary sheet should be 12.06, as that was the rate on June 23. On June 24, there were no phone calls made to this particular record, so the average should still be 12.06. That is where I am running into the problem.
 
Upvote 0
I cannot seem to edit my post, but this eventually needs to be done for an entire month, which is why I'm trying to use a fairly complicated (for me at least) formula to calculate just 2 days.
 
Upvote 0
This is going to get much more complicated.
  • Is each day going to be in a separate sheet ?
  • Is it the case that every time a value is missing, it means no price change and you want to bring forward the previous value ?
  • Also your image is only showing results as percentages which doesn't seem consistent with the nature of the exercise.
You may need a Power Query or VBA solution to do this.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top