AverageIFS based on multiple vlookup Criteria

[Mitchx]

Hi guys,

I am looking for help as I am kinda stuck, I have a formule where I am comparing four columns with an IF/AND formula to a vlookup column to see if a matched value can be found.
Basically every value has to find a matching value before we proceed to averaging the amount corresponding to the four matching criteria.

The formula below is working and gives me a 1 if every criteria is being matched, and a 0 if not. My problem is I want to find the average value (another column named 'ATP combi'!C:C', the amounts can be found here)
But I only want to average the matched criteria, so not the whole column.

=IF(AND(VLOOKUP(merk_input;'ATP combi'!F:F;1;0)=merk_input;VLOOKUP(model_input;'ATP combi'!G:G;1;0)=model_input;VLOOKUP(looptijd_input;'ATP combi'!I:I;1;0)=looptijd_input;VLOOKUP('Input kostprijsanalyse'!G54;'ATP combi'!H:H;1;0)='Input kostprijsanalyse'!G54);1;0)

How would get the average of column C, as mentioned above in this formula?

Please assist me, or feel free to ask any questions!

 

[Alex Blakenburg]

I think I might be misunderstanding what you are trying to do.
Did the following not work for you ?

=AVERAGEIFS('ATP combi'!C:C,
                             'ATP combi'!F:F,merk_input,
                             'ATP combi'!G:G,model_input,
                             'ATP combi'!I:I,looptijd_input,
                             'ATP combi'!H:H,'Input kostprijsanalyse'!G54)

 

[Mitchx]

I think I might be misunderstanding what you are trying to do.
Did the following not work for you ?

=AVERAGEIFS('ATP combi'!C:C,
                             'ATP combi'!F:F,merk_input,
                             'ATP combi'!G:G,model_input,
                             'ATP combi'!I:I,looptijd_input,
                             'ATP combi'!H:H,'Input kostprijsanalyse'!G54)

Hi Alex,

This is exactly what I needed, I made it myself too complicated.
Thank you very much man!

 

[Alex Blakenburg]

No problem. Glad we could help & thanks for letting me know.