Jul 18 2022 07:11 PM
I have two workbooks (attached).
I am trying to retrieve values from one workbook, depending upon an expense category, into the second workbook. (This first workbook [Sample Tolls] has incorrect values resulting from a malfunctioning SUMIF that has been reported her in a previous post) that uses the formula
=SUMIF($B4,"Taxi",$C4:$E4).
In the second workbook [Sample Taxi Expenses] I am trying to use XLOOKUp to match a date and category to retrieve the correct date from the first workbook [Sample Tolls].
The formula I am using is
=XLOOKUP($C$4:$C$34,'[Sample Tolls.xlsx]Tolls'!$A$4:$A$34,'[Sample Tolls.xlsx]Tolls'!$F$4:$F$34,0,1)
This not only creates a SPILL error but also does not return a correct value.
Any idea of what I am doing wrong in BOTH worksheets?
Jul 18 2022 07:46 PM
SUMIF() operates on rows and not on columns.
You don't need to copy the XLOOKUP() down. Because you use multiple criteria cells the formula spills. But the formula is looking for a category in a date column. Not much matching there.
So, I propose a different data structure. Rows without data are omitted.