index match problem

[rjmdc]

this is the formula
INDEX(tblBudgetAmounts[Amount], MATCH([@Parameter], tblBudgetAmounts[Service], 0), 0)*12
i have a column Type
is there a way to add that Type = "Original" to the above formula?

 

[alansidman]

Please upload a sample of your data and a mocked up solution. Do not post pictures as we cannot manipulate data in a picture. Use XL2BB.

 

[rjmdc]

after downloading xl2bb what next?

 

[rjmdc]

Sample.xlsx
	P
18	1500
General

 

[alansidman]

Upload samples as requested.

 

[alansidman]

You need to highlight the entire range of data. You only have one cell.

 

[rjmdc]

You need to highlight the entire range of data. You only have one cell.

Sample.xlsx
	F	G	H	I	J	K	L	M	N	O	P
1	Number Format	Special Function	Check Type				Service	Number	Budget Type	Provider	Amount
2			AgeRestriction				PRA	1	Budget		79469
3			AgeRestriction				MEDICAID FUNDED	1	Budget		74753
4			AgeRestriction				PROVIDER PURCHASED	1	Budget
5			AgeRestriction				STATE FUNDED	1	Budget		15576
6			AgeRestriction				CONTRACTED SERVICES	1	Budget
7			AgeRestriction				TOTAL BUDGET SERVICES	1	Budget		79469
8							Self-Hired Comm Hab	1	Budget		44928
9							Self-Hired Respite	1	Budget
10							Broker	1	Budget		3000
11							Broker Fee	1	Budget		40
12							IDGS Camp	1	Budget
13							IDGS Coaching for Parents and Advocates	1	Budget
14							IDGS Community Classes	1	Budget		11725
15							IDGS Direct Clinician Services	1	Budget
16							IDGS Household Related Items-Services	1	Budget		1500
17							IDGS Interpretation Services	1	Budget
18							IDGS Memberships	1	Budget		1500
19							IDGS Non-Direct Clinician Services	1	Budget
20							IDGS Paid Neighbor	1	Budget		9600
21							IDGS Staffing Support	1	Budget
22							IDGS Transition Program	1	Budget
23							IDGS Transportation	1	Budget		2500
24							Live-in Caregiver	1	Budget
25							DPP Comm Hab	1	Budget
26							DPP Comm Hab	2	Budget
27							DPP Comm Hab	3	Budget
28							DPP Comm Hab	4	Budget
29							DPP Day Habilitation	1	Budget
30							DPP Day Habilitation	2	Budget
31							Contracted Family Support Services	1	Budget
32							DPP Pathway to Employment	1	Budget
33							DPP Pre-Vocational Site Based	1	Budget
34							DPP Pre-Vocational Community Based	1	Budget
35							DPP Respite	1	Budget
36							DPP Respite	2	Budget
37							DPP Respite	3	Budget
38							DPP Supported Employment	1	Budget
39							DPP Supported Employment	2	Budget
40							DPP Supported Employment	3	Budget
41							OTPS Advertisement Costs	1	Budget
42							OTPS Board Stipend-Food Subsidy	1	Budget
43							OTPS Clothing	1	Budget		250
44							OTPS Internet	1	Budget		980
45							OTPS Other Goods and Services Related to Health and Safety	1	Budget
46							OTPS Other Goods and Services that Increase Independence	1	Budget
47							OTPS Personal Use Transportation	1	Budget		190
48							OTPS Phone Service	1	Budget		600
49							OTPS Software Related to Disability	1	Budget
50							OTPS Staff Activity Fees	1	Budget		260
51							OTPS Staff Training	1	Budget
52							OTPS Utilities	1	Budget		720
53							Family Reimbursed Respite	1	Budget
54							Housing	1	Budget		873
55							Administration	1	Budget		175
56							PRA	1	Amendment		82649
57							MEDICAID FUNDED	1	Amendment		68303.95
58							PROVIDER PURCHASED	1	Amendment		3326
59							STATE FUNDED	1	Amendment		14304
60							CONTRACTED SERVICES	1	Amendment
61							TOTAL BUDGET SERVICES	1	Amendment		75781.95
62							Self-Hired Comm Hab	1	Amendment		36115.2
63							Self-Hired Respite	1	Amendment
64							Broker	1	Amendment		3288.75
65							Broker Fee	1	Amendment		43.85
66							IDGS Camp	1	Amendment
67							IDGS Coaching for Parents and Advocates	1	Amendment
68							IDGS Community Classes	1	Amendment		16000
69							IDGS Direct Clinician Services	1	Amendment
70							IDGS Household Related Items-Services	1	Amendment		1500
71							IDGS Interpretation Services	1	Amendment
72							IDGS Memberships	1	Amendment		800
73							IDGS Non-Direct Clinician Services	1	Amendment
74							IDGS Paid Neighbor	1	Amendment		9600
75							IDGS Staffing Support	1	Amendment
76							IDGS Transition Program	1	Amendment
77							IDGS Transportation	1	Amendment		1000
78							Live-in Caregiver	1	Amendment
79							DPP Comm Hab	1	Amendment
80							DPP Comm Hab	2	Amendment
81							DPP Comm Hab	3	Amendment
82							DPP Comm Hab	4	Amendment
83							DPP Day Habilitation	1	Amendment
84							DPP Day Habilitation	2	Amendment
85							Contracted Family Support Services	1	Amendment
86							DPP Pathway to Employment	1	Amendment
87							DPP Pre-Vocational Site Based	1	Amendment
88							DPP Pre-Vocational Community Based	1	Amendment
89							DPP Respite	1	Amendment
90							DPP Respite	2	Amendment
91							DPP Respite	3	Amendment
92							DPP Supported Employment	1	Amendment	AHRC	3326
93							DPP Supported Employment	2	Amendment
94							DPP Supported Employment	3	Amendment
95							OTPS Advertisement Costs	1	Amendment
96							OTPS Board Stipend-Food Subsidy	1	Amendment
97							OTPS Clothing	1	Amendment		250
98							OTPS Internet	1	Amendment		980
99							OTPS Other Goods and Services Related to Health and Safety	1	Amendment
100							OTPS Other Goods and Services that Increase Independence	1	Amendment
101							OTPS Personal Use Transportation	1	Amendment		450
102							OTPS Phone Service	1	Amendment		600
103							OTPS Software Related to Disability	1	Amendment
104							OTPS Staff Activity Fees	1	Amendment
105							OTPS Staff Training	1	Amendment
106							OTPS Utilities	1	Amendment		720
107							Family Reimbursed Respite	1	Amendment
108							Housing	1	Amendment		942
109							Administration	1	Amendment
General

Cells with Data Validation
Cell	Allow	Criteria
G2:G8	List	Sum

 

[Alex Blakenburg]

this is the formula
INDEX(tblBudgetAmounts[Amount], MATCH([@Parameter], tblBudgetAmounts[Service], 0), 0)*12
i have a column Type
is there a way to add that Type = "Original" to the above formula?

Assuming your Return Value table has a columns called Parameter and by Type you mean Budget Type, either of the below should work.
Note: Budget Type does not have any values for Original only Budget and Amendment, so I have used Amendment

=INDEX(tblBudgetAmounts[Amount], MATCH(1, (tblBudgetAmounts[Service]=[@Parameter]) * (tblBudgetAmounts[Budget Type] = "Amendment"), 0), 0)*12

OR

=XLOOKUP(1,(tblBudgetAmounts[Service]=[@Parameter])*(tblBudgetAmounts[Budget Type]="Amendment"),tblBudgetAmounts[Amount],0)*12

 

[rjmdc]

i needed to use the index matxh version
it works
thanks so much

 

[Alex Blakenburg]

Thanks for letting us know. Glad @alansidman and I could help.