Complex Index/Match or Search/Find (VBA would be an option as well)

[LindenWolf]

Good afternoon. I am trying to do a lookup that is a bit more complex than I imagined. I need to basically find a word in a column to determine which value to return. The kicker is that I need to ensure that mutilple finds are a match to pull in the right value. For Example:
Headings i have are in the following order:
ColA-ColG
Fund, Function, Account Org, Pic,LOC,Account

I have a matrix that has the criteria

I need to pull in the Category field of the look up table but I have Location that has several locations in it like 201,203,204etc...each number represents a location the same with Function, Pic, Fund, and GL

In GL if it is blank it means that all GL's are possible, if there is a value then only that value is possible.

I'm happy to attach a file if someone can tell me how to do so.

Please know that I realize there has to be a better way than excel but this is currently my only option at the moment.

Thank you for any help you can provide.

 

[CA_Punit]

Please attach the file using xl2bb addin or else you can provide a link to One drive or google drive

 

[LindenWolf]

I hope this does it correctly.

ExcelForum.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2									Logic and Expectation of what would populate in Column H.
3	FUND	FUNCTION	ACCOUNT	ORG	PIC	LOC	Account Type	Category	Expectation				Categories	Location	Function	PIC	Fund	GL	Acct Type
4	420	41	6299	005	99	999	Expenses		I need the Category name to populate in H where each item is true Example: Column A occurs in several rows assoicated with Column Q Column B occurs in Several rows associated with Column O Column C does not occur at all in Column R but meets the criteria of Blank (In Column R Blank implies all GL's, if a GL is present it can be the only GL that can be used) Column E occurs in several rows associated with column P Column F occurs only in N35 and N36 Column G occurs in several locations associated with Column S The Result in Cell H25 should be M35 or the word Elimination				State				420,410,	5811,5812,	Revenue
5	199	71	6525	750	99	301	Expenses		M27 or Interest Expense Expense would be for this one				State				385		Revenue
6	240	00	5751	000	00	302	Revenue		M10 or Student Activity Fees				State					5839	Revenue
7	420	00	5812	000	00	999	Revenue						Federal					5921,5922	Revenue
8	420	00	5812	000	00	999	Revenue		Note: Data in Column M-S is actually located on the Lookup tab, I brought it over for ease of writing up what I would need				Federal				224,211,255,263,258		Revenue
9	420	41	6299	001	99	999	Expenses						Student Activity Fees	202,203,204,205,206,207,208,209,210,302,303,304,305,306	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	197,199	5753	Revenue
10	420	41	6299	001	99	999	Expenses	#N/A					Student Activity Fees	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	5751'5721'5722'5829	Revenue
11	420	41	6299	002	99	999	Expenses						Student Activity Fees	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	199'420'198	5749	Revenue
12	420	41	6299	002	99	999	Expenses						Student Activity Fees	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	198	5752	Revenue
13	420	41	6299	003	99	999	Expenses						CI, Tax Credits & Philanthropy	201,301,801,208,210	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	199,211,224,240	5748	Revenue
14	420	41	6299	003	99	999	Expenses						CI, Tax Credits & Philanthropy	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	199,198	5744	Revenue
15	420	41	6299	003	99	999	Expenses						CI, Tax Credits & Philanthropy	202,203,204,205,206,302,303,207,208,304	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	196,197,211,224,240,255,263,385,410,420	5744	Revenue
16	420	41	6299	003	99	999	Expenses						Management Services						Revenue
17	420	41	6299	004	99	999	Expenses						Rental Income	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	5743	Revenue
18	420	41	6299	004	99	999	Expenses						After School Programs	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	196		Revenue
19	420	41	6299	009	99	999	Expenses						Instructional	202,203,204,205,206,207,208,209,210,302,303,304,305,306	11,31	00,11,20,21,22,26,27,28,29,30,31,32,91,99	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
20	420	41	6299	009	99	999	Expenses						Special Education	202,203,204,205,206,207,208,209,210,302,303,304,305,306	11,31	23,24,25,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
21	420	41	6299	008	99	999	Expenses						Extra Curricular: Athletics	205,207,209,303	36	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	198,199,211,224,240,255,258,263,385,410,420		Expenses
22	420	41	6299	008	99	999	Expenses						Extra Curricular: Non-Athletics	202,203,204,205,206,207,208,209,210,302,303,304,305,306	36	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	197		Expenses
23	420	41	6299	006	99	999	Expenses						Support Services: Facilities	202,203,204,205,206,207,208,209,210,302,303,304,305,306	51,52	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
24	420	41	6299	006	99	999	Expenses						Support Services: Other Non-Instructional	202,203,204,205,206,207,208,209,210,302,303,304,305,306	12,13,32,33,34,35,53	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
25	420	41	6299	007	99	999	Expenses						Support Services: Administration	202,203,204,205,206,207,208,209,210,302,303,304,305,306	21,23,41,61,81	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
26	420	41	6299	007	99	999	Expenses						Lead Office Expenses	201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
27	420	41	6299	005	99	999	Expenses						Interest Expense	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	71	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
28	420	00	5811	000	00	999	Revenue						Depreciation & Amortization	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	51	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	6449,6524	Expenses
29	420	00	5811	000	00	999	Revenue						After School Programs Expense	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	196		Expenses
30	420	71	6523	005	99	999	Expenses												Expenses
31	420	71	6523	007	99	999	Expenses												Expenses
32	420	71	6523	006	99	999	Expenses												Expenses
33	199	00	5743	000	00	999	Revenue						Depreciation Expense						Expenses
34	199	00	5743	000	00	999	Revenue						Amortization of Bond Cost	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	6525	Expenses
35	420	71	6523	003	99	999	Expenses						Eliminination	999	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
36	420	71	6523	003	99	999	Expenses						Eliminination	999	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Revenue
37	199	00	5744	000	00	208	Revenue						Other Expense	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	194	8954	Loss
38	224	11	6119	006	23	208	Expenses						Other Expense	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	6429,5742	Expenses
39	224	11	6141	006	23	208	Expenses						Other Income	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	194	5748,5744	Revenue
40	224	11	6143	006	23	208	Expenses						Other Income	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	7951	Gain
41	224	11	6146	006	23	208	Expenses						Other Income	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	5742	Revenue
DataDrop

Cell Formulas
Range	Formula
H10	H10	=INDEX(Lookup!$A$24:$A$74,MATCH(1,(IFERROR(FIND($F12,Lookup!$B$24:$B$74,1),"")=Lookup!$B$24:$B$74)))

 

[CA_Punit]

As you have mentioned that H10 should result in Elimination. However if we see the Elimination Details

Book1
	B	C	D	E	F	G	H
1	Categories	Location	Function	PIC	Fund	GL	Acct Type
33	Eliminination	999	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,000,000,000,000,000,000,000,000,000,000		Expenses
34	Eliminination	999	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,000,000,000,000,000,000,000,000,000,000		Revenue
Sheet2

We find that Fund 420 is not available in Categories of Elimination.
Please clarify

 

[LindenWolf]

I think something happened in my upload as i looked at my original excel file and 420 is listed. It should be on my list.

 

[CA_Punit]

Please upload the fresh file

 

[LindenWolf]

Here is the file. Please note that the fund should be in text format otherwise it does weird stuff with the #'s. i see 420 listed below.

ExcelForum.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2									Logic and Expectation of what would populate in Column H.
3	FUND	FUNCTION	ACCOUNT	ORG	PIC	LOC	Account Type	Category	Expectation				Categories	Location	Function	PIC	Fund	GL	Acct Type
4	420	41	6299	005	99	999	Expenses		I need the Category name to populate in H where each item is true Example: Column A occurs in several rows assoicated with Column Q Column B occurs in Several rows associated with Column O Column C does not occur at all in Column R but meets the criteria of Blank (In Column R Blank implies all GL's, if a GL is present it can be the only GL that can be used) Column E occurs in several rows associated with column P Column F occurs only in N35 and N36 Column G occurs in several locations associated with Column S The Result in Cell H25 should be M35 or the word Elimination				State				420,410,	5811,5812,	Revenue
5	199	71	6525	750	99	301	Expenses		M27 or Interest Expense Expense would be for this one				State				385		Revenue
6	240	00	5751	000	00	302	Revenue		M10 or Student Activity Fees				State					5839	Revenue
7	420	00	5812	000	00	999	Revenue						Federal					5921,5922	Revenue
8	420	00	5812	000	00	999	Revenue		Note: Data in Column M-S is actually located on the Lookup tab, I brought it over for ease of writing up what I would need				Federal				224,211,255,263,258		Revenue
9	420	41	6299	001	99	999	Expenses						Student Activity Fees	202,203,204,205,206,207,208,209,210,302,303,304,305,306	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	197,199	5753	Revenue
10	420	41	6299	001	99	999	Expenses	#N/A					Student Activity Fees	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	5751'5721'5722'5829	Revenue
11	420	41	6299	002	99	999	Expenses						Student Activity Fees	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	199'420'198	5749	Revenue
12	420	41	6299	002	99	999	Expenses						Student Activity Fees	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	198	5752	Revenue
13	420	41	6299	003	99	999	Expenses						CI, Tax Credits & Philanthropy	201,301,801,208,210	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	199,211,224,240	5748	Revenue
14	420	41	6299	003	99	999	Expenses						CI, Tax Credits & Philanthropy	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	199,198	5744	Revenue
15	420	41	6299	003	99	999	Expenses						CI, Tax Credits & Philanthropy	202,203,204,205,206,302,303,207,208,304	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	196,197,211,224,240,255,263,385,410,420	5744	Revenue
16	420	41	6299	003	99	999	Expenses						Management Services						Revenue
17	420	41	6299	004	99	999	Expenses						Rental Income	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	5743	Revenue
18	420	41	6299	004	99	999	Expenses						After School Programs	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	196		Revenue
19	420	41	6299	009	99	999	Expenses						Instructional	202,203,204,205,206,207,208,209,210,302,303,304,305,306	11,31	00,11,20,21,22,26,27,28,29,30,31,32,91,99	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
20	420	41	6299	009	99	999	Expenses						Special Education	202,203,204,205,206,207,208,209,210,302,303,304,305,306	11,31	23,24,25,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
21	420	41	6299	008	99	999	Expenses						Extra Curricular: Athletics	205,207,209,303	36	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	198,199,211,224,240,255,258,263,385,410,420		Expenses
22	420	41	6299	008	99	999	Expenses						Extra Curricular: Non-Athletics	202,203,204,205,206,207,208,209,210,302,303,304,305,306	36	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	197		Expenses
23	420	41	6299	006	99	999	Expenses						Support Services: Facilities	202,203,204,205,206,207,208,209,210,302,303,304,305,306	51,52	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
24	420	41	6299	006	99	999	Expenses						Support Services: Other Non-Instructional	202,203,204,205,206,207,208,209,210,302,303,304,305,306	12,13,32,33,34,35,53	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
25	420	41	6299	007	99	999	Expenses						Support Services: Administration	202,203,204,205,206,207,208,209,210,302,303,304,305,306	21,23,41,61,81	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
26	420	41	6299	007	99	999	Expenses						Lead Office Expenses	201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
27	420	41	6299	005	99	999	Expenses						Interest Expense	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	71	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
28	420	00	5811	000	00	999	Revenue						Depreciation & Amortization	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	51	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	6449,6524	Expenses
29	420	00	5811	000	00	999	Revenue						After School Programs Expense	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	196		Expenses
30	420	71	6523	005	99	999	Expenses												Expenses
31	420	71	6523	007	99	999	Expenses												Expenses
32	420	71	6523	006	99	999	Expenses												Expenses
33	199	00	5743	000	00	999	Revenue						Depreciation Expense						Expenses
34	199	00	5743	000	00	999	Revenue						Amortization of Bond Cost	202,203,204,302,206,205,303,208,210,304,201,301	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	6525	Expenses
35	420	71	6523	003	99	999	Expenses						Eliminination	999	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Expenses
36	420	71	6523	003	99	999	Expenses						Eliminination	999	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420		Revenue
37	199	00	5744	000	00	208	Revenue						Other Expense	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	194	8954	Loss
38	224	11	6119	006	23	208	Expenses						Other Expense	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	6429,5742	Expenses
39	224	11	6141	006	23	208	Expenses						Other Income	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	194	5748,5744	Revenue
40	224	11	6143	006	23	208	Expenses						Other Income	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	7951	Gain
41	224	11	6146	006	23	208	Expenses						Other Income	202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,301	00,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,93	00,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37	163,194,196,197,198,199,211,224,240,255,258,263,385,410,420	5742	Revenue
42	420	11	6116	006	11	208	Expenses
43	420	11	6119	006	11	208	Expenses
44	420	11	6119	006	11	208	Expenses
45	420	11	6119	006	23	208	Expenses
46	420	11	6119	006	24	208	Expenses
47	420	11	6119	006	25	208	Expenses
48	420	11	6141	006	11	208	Expenses
49	420	11	6141	006	11	208	Expenses
50	420	11	6141	006	23	208	Expenses
51	420	11	6141	006	24	208	Expenses
52	420	11	6141	006	25	208	Expenses
53	420	11	6146	006	23	208	Expenses
54	420	11	6146	006	24	208	Expenses
55	420	11	6146	006	25	208	Expenses
56	420	11	6149	006	11	208	Expenses
57	420	11	6397	006	11	208	Expenses
58	420	23	6116	006	99	208	Expenses
59	420	23	6119	006	99	208	Expenses
60	420	23	6141	006	99	208	Expenses
61	420	23	6143	006	99	208	Expenses
DataDrop

Cell Formulas
Range	Formula
H10	H10	=INDEX(Lookup!$A$24:$A$74,MATCH(1,(IFERROR(FIND($F12,Lookup!$B$24:$B$74,1),"")=Lookup!$B$24:$B$74)))

 

[CA_Punit]

There is some probelm while pasting with xl2bb. The Text number is converted into Number like 1.63194196197198E+44.

You have to share your data (Google Drive/One Drive).

 

[LindenWolf]

I think i have it. I did it the long way and created a logic to the side that it had to meet each critiera. I reviewed each possible transaction. The indirect was linking to a lookup tab that created the formula automatically so that I didn't have to type it in (i'm lazy that way). So far, it seems to be working. I need to learn how to do VBA loops as i think that would have probably been the best solution to my problem.

Roughly my formula was:

IF(AND(IFERROR(FIND($F330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,11,FALSE)),1),0)>0,
IFERROR(FIND($B330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,12,FALSE)),1),0)>0,
IFERROR(FIND($E330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,13,FALSE)),1),0)>0,
IFERROR(FIND($A330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,14,FALSE)),1),0)>0,
IF(INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,15,FALSE))="",1,IFERROR(FIND($C330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,15,FALSE)),1),0))>0,
IFERROR(FIND($G330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,16,FALSE)),1),0)>0
),"Yes","NO")

Thanks for looking at this for me. I slept on it and this was the fastest way i could think of. If you are doing a VBA code I'd be happy to share the file over google or one drive, but otherwise I think i have it.

 

[CA_Punit]

Ok ... anyway i recommend using search instead of Find