Combining 2 Formulas to Check and Return Value

Occasional Contributor

Need Help 
We have Columns contains numeric value , I need Excel Formula to copy the same cell if only the Digit Number 9 & 8 from right to left equal (50, 52, 54, 55, 56 or 58), other wise return Null. Below sample of the data and Expected Values


The DataExpected Values
7770 4982152Null
12 Replies
best response confirmed by mrxware (Occasional Contributor)



in C2 one way:

Thanks A lot
You're welcome + Thanks for providing feedback


To dynamically assess any sized data set, try:




Replace A1:A19 with your dataset range.

Alternatively with 2021 or 365:

=IF(ISNUMBER(XMATCH(MID(A1:A19,LEN(A1:A19)-8,2),{"50","52","54","55","56","58"})), A1:A19, "null")


Hi @MY (^0 isn't required in your nice MMULT option)


A variant with XOR:



Haha thanks, I woke up this morning asking myself why I put that there!
Can you please explain this Formula ?
Appreciate Your Help
Thanks, It's look simple. Can You Please explain it briefly




So if we digest it in parts:

  1. LET(DATA,A1:A19,...) : allows you to define names/variables where in this case DATA = A1:A19. This way you only need to change the range once for DATA and it updates for all instances of DATA in the formula. The formula you want to execute goes into the "..." space.
  2. MID(DATA,LEN(DATA)-8,2) : generates a dynamic array of the 8th and 9th character in the range i.e. a column of 2 digits (8th and 9th character from right)
  3. --MID(DATA,LEN(DATA)-8,2) : normally the use of "--" converts TRUE and FALSE values to 1 and 0 respectively, but in this instance it converts numerical STRING into NUMBER. If your data range is always NUMBER then this can be disregarded.
  4. --MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58} : Checks if the double digit is equal to 50, 52 ... 58 for each row of your range. This will return an array of size (n x 6) with either TRUE or FALSE values. Rows with one match will have one TRUE in the respective row.
  5. --(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}) : Converts the TRUE or FALSE values to 1 or 0 respectively. Rows with one match will have one "1" in the respective row.
  6. SEQUENCE(6) : Generates an array of size 6 x 1 with integers 1 to 6
  7. MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)) : Matrix multiplies the array of size (n x 6) made up of 1's and 0's with the array of size (6 x 1). The resultant is an array of size (n x 1) whereby rows with one match will have a positive integer in the respective row whilst no matches results in a 0.
  8. =LET(DATA,A1:A19,
    DATA,"Null")) : the curious thing about the IF statement is that the first value must be a TRUE or FALSE value OR alternatively, a number not equal to 0 for TRUE and 0 for FALSE. In step 7, we now have an array of size (n x 1) made up for positive integers and 0's that define our TRUE and FALSE for each row. Plug it into the IF statement and we get DATA if TRUE or Null if FALSE.

This is a mathematical outlook on solving this problem so I highly suggest replicating steps 2 to 8 separately (replace DATA with A1:A19 for steps 2 to 7) to understand what's happening at each step.


Hopefully this helps.

That is absolutely fantastic and helpful
You give me the fish and helped me how to fishing
Appreciate your effort. Thanks