Match Formula

[howard]

I have a spreadsheet with a number of account numbers. I also import account numbers and have set up a match formula to advise whether the account is "Found" or "Not Found"

Where the account number has an Alpha whithin the number the match formula gives me the correct result for Eg 10G551 gives a correct result.

However where there is a number with no Alpha character in the account number an incorrect result is given for eg account 107500 appears in B3, however the match formula comes up as "Not found"

I have attached sample data. Your assistance in resolving this problem will be most appreciated

Account Number Matching.xls
	B	C	D	E
3	107500
4	107700
5	107823
6	107510
7	107710
8	0
9	107501
10	107511
11	107590
12	10G550
13	10G568
14	10G668
15	10G580
16	10G581
17	10G554
18	10G654
19	10G558
20	10G551
21	10G566
22	10G556
UV Sales

Account Number Matching.xls
	A	B	C	D
45	107500	NewAccount
46	107510	NewAccount
47	10G551	Found
UV Sales

 

[njimack]

=IF(LEN(A45)*COUNTIF($B$3:$B$32,A45),"Found","New Account")

Edit - just realised that's not exactly what you want. Try@
=IF(A45="","",IF(COUNTIF($B$3:$B$32,A45),"Found","New Account"))

 

[Aladin Akyurek]

I have a spreadsheet with a number of account numbers. I also import account numbers and have set up a match formula to advise whether the account is "Found" or "Not Found"

Where the account number has an Alpha whithin the number the match formula gives me the correct result for Eg 10G551 gives a correct result.

However where there is a number with no Alpha character in the account number an incorrect result is given for eg account 107500 appears in B3, however the match formula comes up as "Not found"

I have attached sample data. Your assistance in resolving this problem will be most appreciated

Account Number Matching.xls
	B	C	D	E
3	107500
4	107700
5	107823
6	107510
7	107710
8	0
9	107501
10	107511
11	107590
12	10G550
13	10G568
14	10G668
15	10G580
16	10G581
17	10G554
18	10G654
19	10G558
20	10G551
21	10G566
22	10G556
UV Sales

Account Number Matching.xls
	A	B	C	D
45	107500	NewAccount
46	107510	NewAccount
47	10G551	Found
UV Sales

Does...

=IF(A45="","",IF(ISNUMBER(MATCH(IF(ISNUMBER(A45+0),A45+0,A45),$B$3:$B$32,0)),"Found","New Account"))

resolve the problem?

 

[howard]

Hi Neil & Aladin

Thanks for the replies.

The solution provided by neil works perfectly.

Aladin, the Match formula for some unkown reason does not allways give the correct solution

If you want you can send me your email in a private message & I can email you my file so that you can test the data

 

[Aladin Akyurek]

Hi Neil & Aladin

Thanks for the replies.

The solution provided by neil works perfectly.

Aladin, the Match formula for some unkown reason does not allways give the correct solution

If you want you can send me your email in a private message & I can email you my file so that you can test the data

Either...

=IF(A45="","",IF(ISNUMBER(MATCH(IF(ISNUMBER(A45+0),A45+0,A45),$B$3:$B$32,0)),"Found","New Account"))

Or...

=IF(A45="","",IF(ISNUMBER(MATCH(A45&"",$B$3:$B$32,0)),"Found","New Account"))

should work. Maybe not that relevant here, IsNumber/Match is faster than CountIf.

 

[howard]

Hi Aladin

Thanks for the help.

The formula =IF(A45="","",IF(ISNUMBER(MATCH(A45&"",$B$3:$B$32,0)),"Found","New Account")) works perfectly

What is the relevance of using match(A45&"" in this instance in terms of the lookup value?

Your assistance will be most appreciated

 

[Aladin Akyurek]

Hi Aladin

Thanks for the help.

The formula =IF(A45="","",IF(ISNUMBER(MATCH(A45&"",$B$3:$B$32,0)),"Found","New Account")) works perfectly

What is the relevance of using match(A45&"" in this instance in terms of the lookup value?

Your assistance will be most appreciated

B3:B32 appears to house text numbers. The expression A45&"" makes the number in A45 a text number, which we can then match against the text number B3:B32. Othwerwise, matching true numbers against text numbers won't succeed. CountIf cannot distinguish them at all.

 

[howard]

Hi Aladin

Thanks for the explanation and for all your help

Regards

Howard

 

[Aladin Akyurek]

Hi Aladin

Thanks for the explanation and for all your help

Regards

Howard

You are welcome. Thanks for providing feedback.