Combine vlookup with countifs formula

[asmith601]

Hi everyone. I need some help combining a formula. The formula I have below works for me, but there is 1 part that is manually entered which is highlight in red below

=COUNTIFS('Roster Engagement'!D1:FM1,">="&C11,'Roster Engagement'!D1:FM1,"<="&D11,'Roster Engagement'!D15:FM15,'Roster Engagement'!B57)

The D15:FM15 is the row reference which is currently manually done. It is tied to a name so different name, different row number. The formula below automatically retrieves the proper row number when the same is selected:

=VLOOKUP(B2,'Roster Engagement'!A1:IO50,249,FALSE)

I am decent at excel, but simply cannot get the 2 statements combined to fully automate My thought are:

=COUNTIFS('Roster Engagement'!D1:FM1,">="&C11,'Roster Engagement'!D1:FM1,"<="&D11, VLOOKUP(B2,'Roster Engagement'!A1:IO50,249,FALSE) ,'Roster Engagement'!B57)

Any ideas? Thanks in advance!

 

[Marcelo Branco]

Welcome to Mr Excel

Are you saying that
=VLOOKUP(B2,'Roster Engagement'!A1:IO50,249,FALSE)
returns 15 (the row used in the part in red)?

If so, maybe you can replace
'Roster Engagement'!D15:FM15
by
INDEX('Roster Engagement'!D:FM,VLOOKUP(B2,'Roster Engagement'!A1:IO50,249,FALSE),0)

Hope this helps

M.

 

[Jonmo1]

I don't think OP meant that the vlookup literally returns the row number 15.
I think it means the vlookup returns a value from the appropriate row # based on finding the match to B2 in column A..

In that case, try
=COUNTIFS('Roster Engagement'!D1:FM1,">="&C11,'Roster Engagement'!D1:FM1,"<="&D11,INDEX('Roster Engagement'!D1:FM50,MATCH(B2,'Roster Engagement'!A1:A50,0),0),'Roster Engagement'!B57)

 

[asmith601]

That worked out great. Thanks Marcelo!

 

[Marcelo Branco]

I don't think OP meant that the vlookup literally returns the row number 15.
I think it means the vlookup returns a value from the appropriate row # based on finding the match to B2 in column A..

In that case, try
=COUNTIFS('Roster Engagement'!D1:FM1,">="&C11,'Roster Engagement'!D1:FM1,"<="&D11,INDEX('Roster Engagement'!D1:FM50,MATCH(B2,'Roster Engagement'!A1:A50,0),0),'Roster Engagement'!B57)

Yes, i also considered this possibility. But I was not sure if that was the case. That's why I asked.

M.

 

[Marcelo Branco]

That worked out great. Thanks Marcelo!

You are welcome. Thanks for the feedback.

M.