CODEWITHSUNDEEP
excelexcel-formula
TourEiffel
TourEiffel

Reputation: 4434

AND Formula not working as expected - EXCEL

I am facing a strange issue there : 

=AND(MONTH(VLOOKUP(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE)) & YEAR(VLOOKUP(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE))<= MONTH(Octobre!$A$2) & YEAR(Octobre!$A$2))

Where : 

=MOIS(RECHERCHEV(B25;'Liste-Stagiaire'!$1:$1048576;8;FAUX)) & ANNEE(RECHERCHEV(B25;'Liste-Stagiaire'!$1:$1048576;8;FAUX))

Output : 72019

=MONTH(VLOOKUP(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE)) & YEAR(VLOOKUP(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE))

Output : 102019

So that give the formula :

=AND(72019 <= 102019 )

The excepted output would be TRUE But the output I got is FALSE

What am I missing here ?

Upvotes: 1

Views: 70

Answers (2)

Scott Craner
Scott Craner

Reputation: 152595

& turns the numbers into text and text will determine greater than or less than character by character starting on the left 7>1

Use VALUE to turn the text into numbers:

=AND(VALUE(MONTH(VLOOKUP(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE)) & YEAR(VLOOKUP(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE)))<= VALUE(MONTH(Octobre!$A$2) & YEAR(Octobre!$A$2)))

But as @ForwardEd And @Cyryl stated, using dates may be better and avoid the conversion to and from strings, or the fact that 112018>072019

=AND(DATE(YEAR(VLOOKUP(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE)),MONTH(VLOOKUP(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE)),1)<=DATE(YEAR(Octobre!$A$2),MONTH(Octobre!$A$2),1)

Upvotes: 5

Cyril
Cyril

Reputation: 6829

You are making one comparison, so you can compare similar to:

=(Year(VLookUp(B25;'Liste-Stagiaire'!$1:$1048576;8;FAUX))*12)+Value(Month(Search(B25,'Liste-Stagiaire'!$1:$1048576,8,FALSE))<(Year(Octobre!A2)*12+Month(Octobre!A2))

Essentially, instead of making a string or comparison with 72019, you would be looking at total number of months:

2019*12 + 7  <  2019*12 + 10

Note that doing it this way will normalize all months.

Edit:

Will give an example of swapping to index/match to support this:

=(Year(Index(Sheet2!$H$1:$H$200,Match(B25,Sheet2!$A$1:$A$200,0))*12+Month(Index(Sheet2!$H$1:$H$200,Match(B25,Sheet2!$A$1:$A$200,0)))<=(Year(Octobre!A2)*12+Month(Octobre!A2))

I also shortened the lookup/index ranges, which will be a HUGE saver of performance (you had all sheets used, 1:1048576 which eats up a LOT). Found the 8th column ("H") and output from that using index.

Upvotes: 1

Related Questions