Lookup or Match?

[jbodel]

I have a sheet (We will call it sheet 1) that lists a company name and a billing account number (for a service we provide). If this business has multiple locations, they list a secondary account in the next column. However some people have been listing the next account in the same column, but with a comma or semi-colin separating them. Now the other sheet (We will call it sheet 2) shows all the accounts we are charging for this specific service. What we are trying to find is accounts we are not charging. I will try to make an example below. I am trying to find a formula that will look at at all the potential columns in sheet one that has an account number and look to see if it is found on Sheet 2. I was hoping this would be something like a Vlookup, but I think it is more complicated than that.

In the example below (second table below) it shows that we are charging one of the accounts for ABC Company (111111111) but not the second account. We are not charging any of the accounts for DEF Company, etc. I need (on sheet 1, to indicate if we are charging or not.

Does this all makes sense? I hope it does, and I hope there is an easy to understand formula....

Thank you in advance.

Sheet

Customer Name	Billing Act	Additional Billing Act	Additional Billing Act
ABC Company	111111111	22222222
DEF Company	1234; 55555; 6879	1233333
LMNOP Company	99999	8888; 77777

On the second sheet it is just a list of account numbers and if we are charging. So if the account is not listed below, we are not charging.

Customer Name	Account Number	Charge
ABC Company	1111111	Yes
LMNOP Company	77777	Yes

 

[JamesCanale]

How does this work for you?

MrExcelPlayground24.xlsx
	A	B	C	D	E	F	G	H	I
1
2	Customer Name	Billing Act	Additional Billing Act	Additional Billing Act2			Customer Name	Account Number	Charge
3	ABC Company	111111111	22222222				ABC Company	111111111	Yes
4	DEF Company	1234; 55555; 6879	1233333				LMNOP Company	77777	Yes
5	LMNOP Company	99999	8888; 77777
6
7
8
9	Uncharged Accounts
10	ABC Company		22222222
11	DEF Company	1234	55555	6879	1233333
12	LMNOP Company	99999	8888
Sheet10

Cell Formulas
Range	Formula
A10:E12	A10	=LET(z,Table6[Customer Name],a,TEXTSPLIT(TEXTJOIN("!",TRUE,(BYROW(Table6[[Billing Act]:[Additional Billing Act2]],LAMBDA(r,TEXTJOIN(";",TRUE,r))))),";","!",TRUE,1,""),b,XMATCH(VALUE(a),VALUE(Table7[Account Number]),0),c,IF(ISNA(b),a,""),HSTACK(z,c))
Dynamic array formulas.

 

[hungtbatman1]

I have a sheet (We will call it sheet 1) that lists a company name and a billing account number (for a service we provide). If this business has multiple locations, they list a secondary account in the next column. However some people have been listing the next account in the same column, but with a comma or semi-colin separating them. Now the other sheet (We will call it sheet 2) shows all the accounts we are charging for this specific service. What we are trying to find is accounts we are not charging. I will try to make an example below. I am trying to find a formula that will look at at all the potential columns in sheet one that has an account number and look to see if it is found on Sheet 2. I was hoping this would be something like a Vlookup, but I think it is more complicated than that.

Assumption:
1. Sheet1!A1 = "Customer Name", Sheet2!A1 = "Customer Name"
2. Formula for Sheet2!C2

=IFERROR(IF(LOOKUP(2;1/(FIND(B2;INDEX(Sheet1!B$2:C$100;MATCH(A2;Sheet1!A$2:A$100;0);))>0))=1;"Yes");"")
[code]