Forum Discussion
Solved
if the value in one list exists in another array.
Hello, How can it be made to change all the data at once without breaking the formula? Excel file is attached. İF(A8:A109 (bir değer) = Girişler!Y2:Y26 (if on this list); (does not ta...
It's difficult to decipher exactly what you need, but to check if the values in one list exist in another, you can use either COUNTIF (works only with ranges) or ISNUMBER-MATCH (also works with arrays).
Note: the value in cell A8 in your sample file is "MRGYO", whereas the value in cell Y26 on the input sheet is "MGRYO". You will need to correct one or the other for them to match.
Please see the attached workbook for the complete updated formula. This is my best guess at what you wanted...
How about this:
=IF($A8:$A109="", "", LET(
itemCode, PTC!A7:A109,
start, MATCH(Mix!$D$1, PTC!$C$7:$K$7, 0),
BlockStart, MATCH(OQ5, PTC!5:5, 0) + start - 1,
dataPlus, FILTER(PTC!7:109, (COLUMN(PTC!5:5) >= BlockStart) * (COLUMN(PTC!5:5) <= BlockStart + 8)),
range, DROP(dataPlus, 1) / INDEX(OQ4:PB4, SEQUENCE(1, 9, 1)),
veri, DROP(range, 0, -1),
dataSonraki, DROP(range, 0, 1),
çeyrekler, DROP(LEFT(dataPlus, 1), , -1),
sonuç, (veri - dataSonraki * (RIGHT(çeyrekler) <> "3")),
sonuç / $A$2
))
Unfortunately, it is not available for the poor.
It gives the same result as what I did.
