SOLVED

# Combining 2 Formulas to Check and Return Value

Copper Contributor

# Combining 2 Formulas to Check and Return Value

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 Data Expected Values 777797247379 Null 777483910509 Null 777984179917 Null 777505626795 777505626795 777185360938 Null 777501884978 777501884978 777562393745 777562393745 777882465989 Null 777223529392 Null 777585172285 777585172285 7770 4982152 Null 777554567499 777554567499 777145024522 Null 777235624042 Null 777663870157 Null 777504422934 777504422934 777799635466 Null 777866791186 Null 777585726099 777585726099
12 Replies
best response confirmed by mrxware (Copper Contributor)
Solution

# Re: Combining 2 Formulas to Check and Return Value in C2 one way:

``=IF(ISNUMBER(MATCH(IFERROR(MID(A2,LEN(A2)-8,2),""),{"50","52","54","55","56","58"},0)),A2,"")``

Thanks A lot

# Re: Combining 2 Formulas to Check and Return Value

You're welcome + Thanks for providing feedback

# Re: Combining 2 Formulas to Check and Return Value

@mrxware

To dynamically assess any sized data set, try:

=LET(DATA,A1:A19,
IF(MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)),
DATA,"Null"))

Replace A1:A19 with your dataset range.

# Re: Combining 2 Formulas to Check and Return Value

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)

# Re: Combining 2 Formulas to Check and Return Value

A variant with XOR:

``=IF(XOR(LEFT(RIGHT(A2,9),2)*1={50,52,54,55,56,58}),A2,"Null")``

# Re: Combining 2 Formulas to Check and Return Value

Haha thanks, I woke up this morning asking myself why I put that there!

# Re: Combining 2 Formulas to Check and Return Value

Can you please explain this Formula ?

# Re: Combining 2 Formulas to Check and Return Value

Thanks, It's look simple. Can You Please explain it briefly

# Re: Combining 2 Formulas to Check and Return Value

@mrxware

=LET(DATA,A1:A19,
IF(MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)),
DATA,"Null"))

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,
IF(MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)),
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.

# Re: Combining 2 Formulas to Check and Return Value

That is absolutely fantastic and helpful
You give me the fish and helped me how to fishing