View original (French)

RECHERCHEV returns the same result on the following rows

marcogd3 Posted messages 9 Status Member -  
 dani -
I have a table with different data, when I perform a search I get the correct result using the following formula:
=IF(A19="","",VLOOKUP(A19,Article!$A$1:$BS$9505,13))
the result is correct, nothing to say.
As soon as I go to the next line in A20 where there is no search to be done
since the cell is empty by the condition =IF(A20="",""

the result displayed is the same as in the search line A19, as well as in the following lines until there is an effective search.

Thank you in advance for your help.

4 answers

  1.
    Frenchie83 Posted messages 2254 Status Member 339
     
    Hello
    I think the calculation mode of your sheet is manual, it needs to be set to automatic. Please check the following actions
    -Click on the Office button in the top left
    -Select Excel Options (at the bottom)
    -Select "Formulas"
    -In the "Calculation options" section, check "Automatic"
    -OK
    17
    1. Bernard-GE
       
      Thank you very much!!

      Usually, the sheets open in automatic calculation mode, but I didn't think to check this setting (....)!
      0
    2. dani
       
      It helped me a lot, thank you!!!
      0
  2. Gyrus Posted messages 3360 Status Member 526
     
    Hello again,

    Sorry, I made a mistake. I didn't see that your range covered columns A to BS (and not A to B).
    Try with:
    =IF(A19="","",IFERROR(VLOOKUP(A19,Article!$A$1:$BS$9505,13,FALSE),""))

    Best regards.
    0
    1. marcogd3 Posted messages 9 Status Member
       
      Thank you Gyrus for your response.

      It is excellent (it works very well)

      Could you please explain to me what SIERREUR means and what the two quotes after False correspond to?

      Thank you in advance

      Marco
      0
      1. Gyrus Posted messages 3360 Status Member 526 > marcogd3 Posted messages 9 Status Member
         
        If the search does not yield results (in the case of a nonexistent element in the list), the VLOOKUP function returns an error.
        The IFERROR function allows you to account for this case. The returned value is then the one that is between the two quotes after False.

        A+
        0
  3. Gyrus Posted messages 3360 Status Member 526
     
    Hello,

    Are you sure about your formula?
    As written, the search is done in a 2-column table (A:B) and returns a value from the 13th column!

    A+
    -1
    1. marcogd3 Posted messages 9 Status Member
       
      Hello Gyrus

      Yes, I am sure that it is the value of the thirteenth column that is returned, but also for the lines below, as long as there is no search!

      Thank you for being attentive to my problem,

      Marco
      0
  4. DjiDji59430 Posted messages 4283 Registration date   Status Member Last intervention   718
     
    Hello,

    Your search formula is incomplete

    =IF(A19="","",VLOOKUP(A19,Article!$A$1:$BS$9505,13,0))
    -2
    1. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello,

      0 or "FALSE" are completely equivalent to 1 and TRUE.
      0