Forum Discussion
Excel formulas not working from PC to MAC
Thanks. the workbook uses the XLOOKUP function that is only available in Excel in recent versions of Microsoft 365. On a Mac or PC with Excel 2019 or earlier, XLOOKUP does not exist, so the formulas return #NAME!
In the attached version, I have replaced the XLOOKUP formulas with a version that uses VLOOKUP. That should work in all versions of Excel.
Column F contains
=XLOOKUP(A2,Sheet2!A:A,Sheet2!H:H)/14
That should be
=XLOOKUP(A2,Sheet2!B:B,Sheet2!H:H)/14
since you're looking up the Child ASIN.
Thanks. I replaced the formula on Sheet 1, Column F to pull from Sheet 2, Column B (Child ASIN) and from Sheet 2, Column I (Units Ordered) and it still does not work. I attached revised spreadsheet.
The formulas work in Excel 365 (if up-to-date), but some of the formulas return #N/A because the Child ASIN in column A on Sheet1 is not present in column B on Sheet2 and Sheet3.
The Child ASIN B002CO8900, for example, cannot be found on Sheet2 and Sheet3, but is present in cell B3 of Sheet4.
You could add IFERROR to suppress the errors, and use AVERAGE to calculate the averages. See the attached version.
Good morning, it is Monday again and new data going into the reports If you have a chance can you look at Sheet1 again and let me know why formulas not working. I don't think it is operator error this time. Thank you.
It's the same problem as last time.
The Child ASIN B07JRBRNFW in B2 on Sheet1 does not occur in column B of Sheet2.
The Child ASIN B002CO8900 in B3 occurs neither on Sheet2 nor on Sheet3.
I suggested that you use IFERROR and you wrote that you liked that, but you haven't implemented it...
Thank you for that new formula. Very helpful!