I have an MS Excel 365 (with current patches) spreadsheet that tracks mileage and tolls to various locations. I am trying to build in logic that won't populate mileage and tolls if I have 2 entries to the same location on the same day (as it would be one trip not two).
Today I am using VLookUps to populate the mileage and tolls from a separate sheet in the same workbook. They work, but don't have the logic to verify if the destination is the same location on the same day. I have separate VLookUps for Mileage and Tools (as they are separate cells), but I am trying to build this logic into a single formula and have it populate both of the cells.
Spreadsheet details
- Where D2 & D3 are dates
- Where H2 & H3 are locations
- $A$4:$G$210 is a list of locations to search from to match from Column H on the that has Mileage to the locations in column F on the alternate sheet and Tolls in Column G on the alternate sheet.
Both VLOOKUPs work in the cells as stand alone formulas
=VLOOKUP(H3,'Payment info'!$A$4:$G$210,6,0)
=VLOOKUP(H3,'Payment info'!$A$4:$G$210,7,0)
Here is the formula as it stands today (not working):
=IF ($D$2<>$D$3), SetCellValue($R$3,(VLOOKUP(H3,'Payment info'!$A$4:$G$210,6,0)))), (IF(($H$2<>$H$3), SetCellValue($S$3(VLOOKUP(H3,'Payment info'!$A$4:$G$210,7,0)),SetCellValue($R$3,"NOTHING")))
This formula in Cell R4 produces this error message.
It is possible that the values in D2 & D3 (rows 3 & 4) could be the same but the values in H3 & H4 could be different (rows 3 & 4). In the first case, I want the values from the alternate page to populate R3 & S3, but R4 & S4 to be blank or "--". in the second case, I want values to be populates in R5 & S5, but not R4 & S4.
If the dates are the same, then I want to compare the values from H3 & H4 (locations) to see if they are the same. If they are then blank out R4 & S4. If they are different, then populate R3 & S3 with the values from the VLookUp on the alternate sheet.
Screenshot and mock up of data in Columns R & S. Note Row 4 being populated with "--" :
This all makes sense to me so if it's not clear, please let me know.
IF
right after the logical test. Everything that is coming after it is not evaluated and because you have closed the parenthesis, Excel does not understand why there are more parts to yourIF
. That's why it shows the error message.IF($D$2<>$D$3,SetCellValue($R$3,(VLOOKUP(H3,'Payment info'!$A$4:$G$210,6,0))),IF($H$2<>$H$3, SetCellValue($S$3,(VLOOKUP(H3,'Payment info'!$A$4:$G$210,7,0))),SetCellValue($R$3,"NOTHING")))
.