Forum Discussion

abhitiwa's avatar
abhitiwa
Icon for Helper I rankHelper I
8 years ago

Lookupvalue multiple conditions greater / lesser than date

Hello

 

I am performing a LOOKUPVALUE function to find out a value from a different column using multiple conditions. One of them uses a date value to be compared with another date value.

 

see the last comparison in the example below - 

 

sale = LOOKUPVALUE(abc[sale],abc[Network],xyz[Network],abc[Country],xyz[Country],abc[Start Date],"<="&xyz[Date])

 

this does not work and throws an error - 

Function 'LOOKUPVALUE' does not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

 

however, all the columns i.e. abc[Start Date] & xyz[Date] are already in DATE format. Please HELP.

 

Thanks a lot!

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi abhitiwa

    I can reproduce your problem.

    when i modify your formula as below, it can work well.

    Column = LOOKUPVALUE(abc[sale],abc[Network],xyz[Network],abc[Country],xyz[Country],abc[Start Date],xyz[Date])

    As I see, when you use " "<="&xyz[Date]" in the last parameter of LOOKUPVALUE, it is considered as a text value instead of a date type value.

    As you want to apply multiple conditions, you can refer to this article.

    If you have any question, please feel free to ask me and show some example data and expected result.

     

    Best Regards

    Maggie