Way to verify information

[Kiloelectronvolt]

Hi All,

I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference:

Business name in A1 and address in B1.

Example: Ebay 1123 Fake Street

On my second sheet, I want to type in Ebay, and if I type in the wrong address, it will highlight the address red .

 

[Kiloelectronvolt]

Hi Punit!

I think we got crossed since the last formula. It should only turn red once the wrong number is entered.

10010, 10100, and 10200 are all ebay and should stay unformulated.

If you typed ebay and 10300 it should turn red, because that doesn't match.

 

[CA_Punit]

Sorry i didnt go through

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G
1	ebay	10010			ebay	10019	FALSE
2	ebay	10100
3	ebay	10200
4	ayush	10300
5	ayush	10400
Sheet1

Cell Formulas
Range	Formula
G1	G1	=NOT(ISNUMBER(MATCH(LEFT(F1,3)+0,TRUNC(1/(1/(IF(E1=$A$1:$A$5,$B$1:$B$5)))/100),0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1	Expression	=NOT(ISNUMBER(MATCH(LEFT(F1,3)+0,TRUNC(1/(1/(IF(E1=$A$1:$A$5,$B$1:$B$5)))/100),0)))	text	NO

 

[Kiloelectronvolt]

Punit, I got it to work with your numbers, but not mine.

After reviewing, I see your column B is 5 digits, whereas my column B is just the 3 digits. If we remove the LEFT,3 command, I think it will work? But I tried removing it and it broke my formula. IDK how to remove it properly.

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G
1	ebay	100			ebay	10301	TRUE
2	ebay	101
3	ebay	102
4	ayush	103
5	ayush	104
6
Sheet1

Cell Formulas
Range	Formula
G1	G1	=NOT(ISNUMBER(MATCH(LEFT(F1,3)+0,IF(E1=$A$1:$A$5,$B$1:$B$5),0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1	Expression	=NOT(ISNUMBER(MATCH(LEFT(F1,3)+0,IF(E1=$A$1:$A$5,$B$1:$B$5),0)))	text	NO