Forum Discussion

deniztopcu's avatar
deniztopcu
Brass Contributor
Feb 02, 2025

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 take any action. Therefore, all multipliers become 1.)  ;  İNDİS(OQ4:PB4;SIRALI(1;9;1) )  (If it's not in the list, you run this formula.)     )

 

=EĞER($A8:$A109="";"";LET(
itemCode;PTC!A7:A109;
start;KAÇINCI(Mix!$D$1;PTC!$C$7:$K$7;0);
BlockStart; KAÇINCI(OQ5;PTC!5:5;0)+start-1;
dataPlus;FİLTRE(PTC!7:109;((SÜTUN(PTC!5:5)>=blockStart)*(SÜTUN(PTC!5:5)<=blockStart+8))
range;BIRAK(dataPlus;1) ) /    İNDİS(OQ4:PB4;SIRALI(1;9;1))  ; 
veri;BIRAK(aralık;;-1);
dataSonraki;BIRAK(aralık;;1);
çeyrekler;BIRAK(AL(dataPlus;1);;-1);
sonuç;(data-dataNext*(SAĞDAN(çeyrek)<>"3"));
sonuç/$A$2
))





I want results like yellow products where I want them.

 

  • 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...

  • aasimtek's avatar
    aasimtek
    Copper Contributor

    To check if values in one list exist in another array and change all the data at once without breaking the formula in Excel, you can use a combination of functions like MATCH, ISNUMBER, and IF. Here’s a step-by-step guide to achieve this:

    Step-by-Step Guide
    1. Prepare Your Data
    List 1: Assume this list is in column A (e.g., A2:A10).

    List 2: Assume this list is in column B (e.g., B2:B10).

    2. Use the MATCH Function
    The MATCH function searches for a value in a range and returns the relative position of that item.

    excel
    Copy
    =MATCH(A2, $B$2:$B$10, 0)
    This formula will return the position of the value in A2 within the range B2:B10. If the value is not found, it will return an error.

    3. Use the ISNUMBER Function
    The ISNUMBER function checks if the result of the MATCH function is a number (i.e., the value exists in the second list).

    excel
    Copy
    =ISNUMBER(MATCH(A2, $B$2:$B$10, 0))
    This formula will return TRUE if the value in A2 exists in B2:B10, and FALSE otherwise.

    4. Use the IF Function
    The IF function can be used to return a specific value based on whether the ISNUMBER function returns TRUE or FALSE.

    excel
    Copy
    =IF(ISNUMBER(MATCH(A2, $B$2:$B$10, 0)), "Exists", "Does Not Exist")
    This formula will return "Exists" if the value in A2 is found in B2:B10, and "Does Not Exist" if it is not found.

    5. Apply the Formula to the Entire List
    To apply this formula to the entire list in column A, drag the fill handle (a small square at the bottom-right corner of the cell) down to copy the formula to other cells in the column.

    Dynamic Arrays (Excel 365 and Excel 2021)
    If you are using Excel 365 or Excel 2021, you can use dynamic arrays to spill the results automatically.

    Enter the Formula in the First Cell:

    excel
    Copy
    =IF(ISNUMBER(MATCH(A2:A10, B2:B10, 0)), "Exists", "Does Not Exist")
    Press Enter:

    The result will automatically spill down the column, applying the formula to the entire range A2:A10.

    Notes
    Absolute References: Use absolute references ($B$2:$B$10) for the second list to ensure the range does not change when copying the formula.

    Error Handling: If you want to handle errors more gracefully, you can use the IFERROR function.

    By following these steps, you can efficiently check if values in one list exist in another array and apply changes across the entire dataset without breaking the formula.

     

  • djclements's avatar
    djclements
    Bronze Contributor

    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
    ))
    

     

     

    • deniztopcu's avatar
      deniztopcu
      Brass Contributor

      Unfortunately, it is not available for the poor.
      It gives the same result as what I did.

Resources