INDEX/MATCH on 4 sets of criteria

[hiker8117]

I am attempting to pull in select data from a Project Worksheet1 into an Expenditure Worksheet2 within the same workbook using 4 set of criteria in the Expenditure Worksheet2


The 4 sets of Match criteria found in the Expenditure Worksheet2 is as follows:

FY-18 Project A1 Labor Dollars WRKLD


I have tried numerous times with Index/Match only to get zero results.

I want to pull in labor dollars for a specific project in a fiscal year under WRKLD.

Any help would be appreciated.

Thank you

Gene Haines

<tbody>
[TD="class: xl76, colspan: 3"][/TD]
[TD="class: xl63, width: 105"][/TD]
[TD="class: xl63, width: 105"][/TD]
[TD="class: xl63, width: 123"][/TD]
[TD="width: 119"][/TD]
[TD="width: 119"][/TD]

[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl77, colspan: 3"][/TD]
[TD="class: xl77, colspan: 3"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63"][/TD]

</tbody>

 

[hiker8117]

I will try this.

Thank you for your input.

Gene Haines

 

[hiker8117]

Forgot to thank you for your input. Thank you

Gene Haines

 

[hiker8117]

OMG..can't thank you enough. Totally appreciate your time as it worked perfectly. There will be 2 separate worksheets in the workbook and all I need to do is add the name of the worksheet in the formula. You saved me a ton of time as there are over 40 projects wherein I am tracking the labor and mat expenditures along with remaining funds available

Gene Haines

 

[hiker8117]

Just curious. I placed a + sign after the second Match and got "too few arguments. Can you only have 2 Match statements in an argument and then the next set must be separated with a comma in order for the formula to work?

Thanks again

 

[jorismoerings]

Hi,

Can you show in the formula where you've placed the + sign?

 

[hiker8117]

Hi,

Can you show in the formula where you've placed the + sign?

+ Sign is bolded and I get an error message that says: Too few arguments. Once I replace the + sign with the coma in your original formula it works.

=OFFSET($A$3,MATCH(A17,$A$5:$A$10,0)+MATCH($E$25,$B$5:$B$6,0)+MATCH($A$25,$C$3:$H$3,0)+MATCH($B$25,$C$4:$D$4,0))

 

[jorismoerings]

OK,

By placing the + sign you remove the split between columns to the right and rows up or down and because those are necessary for the OFFSET function subsequently the error "too few arguments" will pop up.

In my formula the first MATCH function will go down to the correct project; the second match will go down to the correct category line, the third MATCH will go to the correct column for the fiscal year while the fourth match will go to the correct column for labor or materials.

Just curious: my example didn't had the + sign where you've placed it. What was your reasoning for doing so?

 

[hiker8117]

OK,

By placing the + sign you remove the split between columns to the right and rows up or down and because those are necessary for the OFFSET function subsequently the error "too few arguments" will pop up.

In my formula the first MATCH function will go down to the correct project; the second match will go down to the correct category line, the third MATCH will go to the correct column for the fiscal year while the fourth match will go to the correct column for labor or materials.

Just curious: my example didn't had the + sign where you've placed it. What was your reasoning for doing so?

Thank you for the explanation. I totally understand the offset formula now.

It was actually a data entry error on my part that caused it. I placed a + sign in lieu of the comma and got the too few arguments.

Again...thanks for your help