EN VI

Excel - Wondering how to fill a cell with multiple criteria from other ranges?

Excel - Wondering how to fill a cell with multiple criteria from other ranges

I've been tinkering with excel all day trying to learn some stuff and I came up with this scenario:

teams )

what I'm trying to do is automatically place a number in c12:i25... IF there is a corresponding name in c2:i11, and the name is next to a number in j2:k25.

For example: if I was to remove NICK from j2, then c12:i12 would disappear. or if I was to remove NICK from c2:i2, c12:i12 would disappear.

I've tried a bunch of different formulas I found on here, including some from my other question on stack overflow. I can't seem to iron out a logical formula.

I'm just using this to learn. Let me know if something isn't clear!

Solution:

Try something along the lines of:

• Formula used in cell C12

=LET(_N, $B12:$B25, IF(TAKE(1-ISNA(XMATCH(TEXTAFTER($J2:$J25,"-"),C2:C11)),ROWS(_N)),_N,""))

Or, not using the B12:B25 as reference to output:

=LET(
     _N, TOCOL($J2:$J25,1),
     IF(ISNA(XMATCH(TEXTAFTER(_N,"-"),C2:C11)),"",TEXTBEFORE(_N,"-")))

Or, could instead of using TOCOL() use _LastRow to get the last row in the range J2:J25

=LET(
     _LastRow, MATCH(2,1/($J:$J<>"")),
     _Teams, $J2:INDEX($J:$J,_LastRow),
     IF(ISNA(XMATCH(TEXTAFTER(_Teams,"-"),C2:C11)),"",TEXTBEFORE(_Teams,"-")))

Tags