Cell’s value to be MATCHED with the last digit of another cell

[hsandeep]

Cell D3 generates values
F2 also generates value but is of MAXIMUM 1 digit.
I need
G3=1 IF D3’s value ENDS WITH value of F2 else 0

How to accomplish?
Thanks in advance.

Book2.xlsx
	D	E	F	G
2			E
3	42000CE			1
4	42000CC			0
5	41E			1
6	E			1
7	ABCD			0
Sheet5

 

[Sufiyan97]

Try

=N(RIGHT(D2)=$F$1)

 

[Micron]

Nice, but the rows are off by one.

 

[hsandeep]

Nice, but the rows are off by one.

"Off by one"...my actual data range is different i.e. AQ51:AQ78

 

[Micron]

Can only go by what you posted.

 

[hsandeep]

Can only go by what you posted.

That's ok. But will the formula of Sufiyan97 work for my range OR more polished/versatile formula is required?

 

[Sufiyan97]

That's ok. But will the formula of Sufiyan97 work for my range OR more polished/versatile formula is required?

It will work, just change it to correct cell references.

 

[Sufiyan97]

Another formula in column I

Book4
	C	D	E	F	G	H	I	J
1
2				E
3		42000CE			1	1	1
4		42000CC			0	0	0
5		41E			1	1	1
6		E			1	1	1
7		ABCD			0	0	0
8
9
Sheet1

Cell Formulas
Range	Formula
H3:H7	H3	=N(RIGHT(D3)=$F$2)
I3:I7	I3	=(--(RIGHT(D3)=$F$2))

 

[hsandeep]

Another formula in column I

Book4
	C	D	E	F	G	H	I	J
1
2				E
3		42000CE			1	1	1
4		42000CC			0	0	0
5		41E			1	1	1
6		E			1	1	1
7		ABCD			0	0	0
8
9
Sheet1

Cell Formulas
Range	Formula
H3:H7	H3	=N(RIGHT(D3)=$F$2)
I3:I7	I3	=(--(RIGHT(D3)=$F$2))

Formula of column I works. 1 query, there is neither 1 nor 0 in the formula but still it populates 1 or 0 as required, how?

 

[Sufiyan97]

Formula of column I works.

So does column H formula have any issue?

1 query, there is neither 1 nor 0 in the formula but still it populates 1 or 0 as required, how?

It is becuase this part of formula RIGHT(D3)=$F$2 will return TRUE and FALSE and then "--" will convert TRUE into 1 and FALSE into 0


you can look here:

The double negative in Excel formulas

The double negative coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. This is a useful technique in many advanced formulas that work with cell ranges.

exceljet.net

While in column H formula N() will convert it TRUE into 1 and so on.