View original (French)

Copy values if two labels are identical

Solved
moonya22 Posted messages 5 Status Member -  
moonya22 Posted messages 5 Status Member -
Hello,

I have a sheet containing two tables. One table includes all the item labels (this is the table where I want to copy my values) and another table has only some item labels.

To make it clearer, let me show you:



For example, fresh apricot, apricot half, dried apricot, and wooden stirrer are listed in the second table, but there are some items that do not appear in the first table. Therefore, I would like to copy the values from the first table (VAL column) to the values of the second table (empty VAL column) but only for the common items.

Thank you in advance!

1 answer

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    assuming, which the image does not show, that your image is placed in A1 and thus that the VAL column is column J, there are two possible options as you do not specify:
    either search with only the product name in column H with this formula, in J2
    =IFERROR(VLOOKUP(H2,A:C,3,FALSE),"not found")
    or include the packaging (it is not always the same in both tables
    so in this case:
    =IFERROR(INDEX($C$2:$C$500,MATCH(H2&I2,$A$2:$A$500&$B$2:$B$500,0)),"not found")
    this last formula is an array formula, it must be entered by pressing Enter while holding down the ctrl and shift keys
    (it automatically displays in curly braces in the formula bar)
    it is quite heavy and it is better to limit the addresses to the number of useful rows (here 2 to 500)
    in both cases, the IFERROR code prevents a default display in case of absence. You can place whatever you want between the last two quotes or leave them empty to have a "blank" cell

    best regards

    --
    The quality of the response depends mainly on the clarity of the question, thank you!
    0
    1. moonya22 Posted messages 5 Status Member
       
      Hello,

      Thank you for your response, I tried to do it but it copies the values. I am attaching a new screenshot with the appropriate cells. I would like to know what the values 3;0 correspond to in the first condition you sent me?

      .

      Thanks again.
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hello

      apparently you are not looking for the right value.
      It’s the value in M that needs to be found in D, unless I misunderstood
      so in O2 the formula should be:
      =IFERROR(VLOOKUP(M2,D:F,3,0),"")

      the formula:
      • takes the value of M
      • looks in column D (the first of the specified range) to find on which row it is located
      • returns the value from the same row of the 3rd column (hence the 3) of the range, which is F

      the 0 is an Excel code that requires the formula to find only exact values. Without this 0, the formula finds the row of the closest lower value provided that the range is sorted in ascending order (alphabetically or numerically) according to the first column
      best regards
      0
    3. moonya22 Posted messages 5 Status Member
       
      Great! Thank you for all your very clear explanations! I will try on slightly more complex tables.

      Thanks again and have a nice day!

      Best regards.
      0