Value from list depending on conditions for Google sheets

[yomarcos]

Hi everyone,

I have been working on this for 2 days straight and I can't figure it out.

I have a list of employees. Depending on the dates and hours needed for every project I get True or False if there is a conflict. I want a formula that provides me with an alternative when there is a conflict. So in H2 the next option should be James. Thomas is in conflict but Daniel (next on the list) is also in conflict. Please help!!

 

[Marcelo Branco]

Welcome to Mr Excel forum

See if this works for you
H2 copied down
=IF(--G2=0,"",INDEX(INDEX(A$13:B$18,0,MATCH(B2,A$12:B$12,0)),AGGREGATE(15,6,(ROW(INDEX(A$13:B$18,0,MATCH(B2,A$12:B$12,0)))-ROW(A$13)+1)/ISNA(MATCH(INDEX(A$13:B$18,0,MATCH(B2,A$12:B$12,0)),F$2:F$10,0)),COUNTIFS(B$2:B2,B2,G$2:G2,G2))))

Hope this helps

M.

 

[yomarcos]

Welcome to Mr Excel forum

See if this works for you
H2 copied down
=IF(--G2=0,"",INDEX(INDEX(A$13:B$18,0,MATCH(B2,A$12:B$12,0)),AGGREGATE(15,6,(ROW(INDEX(A$13:B$18,0,MATCH(B2,A$12:B$12,0)))-ROW(A$13)+1)/ISNA(MATCH(INDEX(A$13:B$18,0,MATCH(B2,A$12:B$12,0)),F$2:F$10,0)),COUNTIFS(B$2:B2,B2,G$2:G2,G2))))

Hope this helps

M.

Hi Marcelo,

I think that would work perfectly. However, my boss wants me to use Google sheets instead of Excel (not happy about it) and the aggregate function is not recognised.

My apologies, I should have mentioned that before

 

[Fluff]

In future if you want an answer for Google sheets please say so & post in the General Discussion & Other Applications section.
I have moved it for you this time.

 

[Marcelo Branco]

think that would work perfectly. However, my boss wants me to use Google sheets instead of Excel (not happy about it) and the aggregate function is not recognised.

I used AGGREGATE as a kind of SMALLIFS, i.e, SMALL with conditions

I don't have any experience with Google sheets, but maybe you can adapt it using something like
=ArrayFormula (INDEX(INDEX(...),SMALL(IF(conditions,ROW(INDEX(...))-ROW(A$13)+1),COUNTIFS(...))))

M.

 

[yomarcos]

I used AGGREGATE as a kind of SMALLIFS, i.e, SMALL with conditions

I don't have any experience with Google sheets, but maybe you can adapt it using something like
=ArrayFormula (INDEX(INDEX(...),SMALL(IF(conditions,ROW(INDEX(...))-ROW(A$13)+1),COUNTIFS(...))))

M.

I tried, but I am not sure what you mean with conditions in the SMALL(IF(

 

[Marcelo Branco]

This worked for me in a Google sheet
H2 copied down
=ArrayFormula(IF(--G2=0;"";INDEX(INDEX(A$13:B$18;0;MATCH(B2;A$12:B$12;0));SMALL(IF(ISNA(MATCH(INDEX(A$13:B$18;0;MATCH(B2;A$12:B$12;0));F$2:F$10;0));ROW(INDEX(A$13:B$18;0;MATCH(B2;A$12:B$12;0)))-ROW(A$13)+1);COUNTIFS(B$2:B2;B2;G$2:G2;G2)))))

M.

 

[Marcelo Branco]

That's it

 

[Marcelo Branco]

A better picture - you can see the entire formula

 

[Marcelo Branco]

My version uses semicolons as argument separator in formulas - i think you should substitute them by commas

M.