Copy values if two labels are identical
Solved
moonya22
Posted messages
5
Status
Membre
-
moonya22 Posted messages 5 Status Membre -
moonya22 Posted messages 5 Status Membre -
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!
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 réponse
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!
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!
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.
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:
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
Thanks again and have a nice day!
Best regards.