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?

 

[rjmdc]

sorry its broken
this is the formula nthat needs to be updated:
=@INDEX(tblBudgetAmounts[Amount], MATCH([@Parameter], tblBudgetAmounts[Service], 0), 0)

 

[Alex Blakenburg]

You need to elaborate, that is the formula I updated.
The issues are:
• you did not specify where the formula goes I assume in the green table
• @Parameter indicates there is a column called Parameter in that same table but your XL2BB shows no such column
• You asked for an additional Criteria of Type = Original.
There is no Type field in the Budget Table, so did you mean the column Budget Type ?
There is no Original in that field did you mean Amendment or Buget ?
Did you want that hard coded or will that be in one of the columns in the green table and if so which field.

Note: the formula you sent me above has an @ at the beginning. Are you definitely using MS 365 ? The formula should not have an @ at the beginning.

20231121 Index Match Xlookup multiple criteria rjmdc.xlsx
	E	F	G	H
1	Parameter	Number Format	Special Function	Check Type
2	MEDICAID FUNDED	819647.4		AgeRestriction
Sheet1

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

 

[rjmdc]

ok i think i will need to pay for the person who built this to fix it
somehow column B was an address menaing the formula with out the =
then column D was value with the = as a full formula
when the process runs it gives a #NA error
that menas it is not reading the formula correctly
somehow i think it needs the @Index for the process to work

 

[Alex Blakenburg]

That gets confusing. The columns you are now referencing have not factored into the equation at all prior to this.
If you want to show us all the columns (or share the workbook via google drive or drop box etc) and tell us what it is doing and not doing and what you want it to do, we should be able to help.

 

[rjmdc]

sorry i sent the 2 tables that refrence eachother
i cannot send the entire workbook as there is too much confidential information
column b states what column D will need to do as a formula
it keeps failing

 

[Alex Blakenburg]

Like I said. Column B and D have not been mentioned before now and we have no visibility of what is in those columns or how it relates to the other 2 tables that are in your XL2BB

 

[rjmdc]

hi
i used your xlookup fomula but i did need to add the @ at the beginiing
it works like a dream
thanks do much for steering me in the right direction

 

[Alex Blakenburg]

You're welcome. Glad you got it working.