As said in the title the Italian version of the 2021 Excel does not give the correct results ... as shown in the attached sample ... and surely due the my incorrect usage of it! Please enlighten me, thank you,


Antonio

Attached Files

Function-CERCA-problem.xlsx (11.1 KB, 8 views)

Hi
the CERCA function ( LOOKUP) returns the largest value smaller than the lookup value when not found. You should not use it, as it was only created for compatibility reasons.
Instead you can use the CERCA.X function ( XLOOKUP) which should be available in XL2021 AFAIK
5 i am using O365 but cannot change this is my profile as admin decided against it, there being numerous versions of O365)

Attached Files

Function-CERCA-problem.xlsx (12.3 KB, 4 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Excellent solution, Mr. Pecoflyer, THANK YOU VERY MUCH!
I will flag this thread as SOLVED although there was another small question remained unanswered: the vertical counting of the col. Y alone values, for some for me mysterious reasons, doesn't work! Never mind ... I will further investigate it myself ...
GRAZIE MOLTE! Antonio

You're welcome
Your formula =COUNTIF(Y7:Y9;">0") will only work for numbers;"03" e.g. is text, so depending on what you will input in the cell when the value is not found, there are different possibilities.
Let me know what you want to do

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

There is a big dilemma ... if I digit 3 as the first value to be searched the CERCA.X function will not find it ... so I'm obliged to specify all values from 1 to 9 with a leading 0 ! But then the counting will not function ... since fo me the CERCA.X service is more important I'm ready to renounce to the counting function ... if for such task there is no feasible solution ... therefore thank you anyway Mr. PECOFLYER! Antonio

Does this help ?

Attached Files

Function-CERCA-problem(2).xlsx (11.7 KB, 3 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Your trick did function, SIGNOR Pecoflyer!
Very obliged! you are a genius ... thank you very much again, Antonio

You're welcome

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post