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 .

 

[CA_Punit]

Try this in

Book4
	A	B
1	ebay	11101
2
3
4
5
6
7
8
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=LEFT(VLOOKUP(A1,Sheet2!A:B,2,0),3)+0<>TRUNC($B1/100)	text	NO

 

[Kiloelectronvolt]

Try this in

Book4
	A	B
1	ebay	11101
2
3
4
5
6
7
8
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=LEFT(VLOOKUP(A1,Sheet2!A:B,2,0),3)+0<>TRUNC($B1/100)	text	NO

With one slight modification, changing ($B1/100) to ($E1/100)

IT WORKED. Dude. Thank you. I literally had the hardest time trying to explain this situation. I was trying to spare you some of the boring details. lol..


THANK YOU

 

[CA_Punit]

Finally,

We solved it. ?

 

[Kiloelectronvolt]

Finally,

We solved it. ?

Ca_PUNIT I have a little issue that I would appreciate your insight on if you're willing!

If you own 3 addresses you would have 3 project numbers. 1 for each address.

Ca_Punit 307
Ca_Punit 308
Ca_Punit 309

What's happening now is if I type in 30701, it's good. But if I type in 30801 or 30901 it turns red.

It's only grabbing the first one in the list... and saying the others are not true.

Current code is

=VLOOKUP($B2,JOBS!A:B,2,0)+0<>TRUNC($E2/100)

 

[CA_Punit]

Yes Vlookup only returns the FIrst value. Can you tell me how many unique combination can CA_Punit return. I mean

Ca_Punit 307
Ca_Punit 308
Ca_Punit 309

This returns 3 unique combination. So What is the maximum unique combination can a address return????

 

[CA_Punit]

This formula may slow down as i have selected full range A:A and B:B
Just select the data range in the lookup sheet

=ISERROR(SUM(MATCH(LEFT(B1,3)+0,TRUNC(1/(1/(IF(A1=Sheet3!A:A,Sheet3!B:B)/100))),0)))

 

[Kiloelectronvolt]

=ISERROR(SUM(MATCH(LEFT(E1,3)+0,TRUNC(1/(1/(IF(B1=Sheet3!$A$1:A200,Sheet3!$B1:$B200)/100))),0)))

I tried using this formula. And everything comes back red.

E1 is my project number, and B1 is my Customer

 

[CA_Punit]

Sheet3!$A$1:A200
You didn't lock A200

 

[Kiloelectronvolt]

So it should be this? This makes every number red for me.

=ISERROR(SUM(MATCH(LEFT(E1,3)+0,TRUNC(1/(1/(IF(B1=Sheet3!$A1:$A200,Sheet3!$B1:$B200)/100))),0)))

 

[CA_Punit]

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

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

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