Issues with formulas that use cells with vlookup

Copper Contributor

Hello everyone, I need some help. I have 2 cells on a workbook that get its information via a vlookup and pulling information for those cells from another excel sheet hosted on a sharepoint. =LOOKUP(@A2:A8596,'https://sitecom/[sourceInfo.xlsx]RUBYRaw'!$Q2:$Q8596,'https://site.com/[destinationDoc]RUBYRaw'!$O2:...)

 

I edited that vlookup so it may be wrong here but it works. 

 

So this pulls in 2 different data types 1 is a string the other is a date. I need to write a formula that says if the text in cell A is Registered, and the date in Cell B is not older than 10 days past a specific date then it is to write the string "Compliant", If the string is not "Registered" and / or the date is more than 10 days past the specified date. It is to populate the cell with "Not Compliant"

 

The formula works perfect if the 2 cells are not using vlookup, and rather have static content in the cell. Using vlookup though it will just always populate what ever the first action of the if is. Here is the formula I am using 

 

=IF(AND(AO2="Registered",AQ2>=DATE(2022, 5, 24)-10),"Compliant","Non-Compliant")

 

again this works if data is static but fails if it is populated with vlookup. Any help is appreciated. Thank you. 

1 Reply

@quarinteen316 

 

That means either the string returned by the VLookup function contains leading or trailing spaces or the date returned by the VLookup function is not a proper date but a date as a string which you are comparing with a real date.

 

To check that, place the formula =LEN(AO2) in a blank cell and check the number of characters returned by the Len function matches the length of the string returned by the VLookup function and place the formula =ISNUMBER(AQ2) in a blank cell and if this formula returns True that means the date returned by the VLookup function is a real date but if it returns False, that means the date is a text string.