Excel - search for missing values and return these

问题:

This gets a bit too expert for me, hopefully one of you can help me!

See the image for what I am trying to achieve. The red text shows what I want. List3 contains all the possible values for List1. List2 displays the currently active values for List1, and I want to return the missing values based on List3.

I'm guessing I'm looking at a MATCH/INDEX/IF function but I cannot figure this one out. Anyone out there willing to give me a hand? <3 Thanks a bunch!

回答1:

Put this formula in F4 and fill down.

=INDEX(C:C, AGGREGATE(15, 7, ROW(C$2:C$11)/NOT(COUNTIFS(B:B, C$2:C$11, A:A, E4)), COUNTIF(E$4:E4, E4)))

Of course, this formula is dependent on the list of 'numbers' in E4:E7 being accurate and sufficiently populated to catch all missing entries.

回答2:

Reference the image below. Use this formula to get the number missing:

=SUMPRODUCT(--(A2:A40<>A1:A39))*ROWS(C2:C12)-ROWS(A2:A40)

Change A40 to the last cell of your data in column A and A39 to the second to last cell.

Then enter this helper column as an array formula (Ctrl+Shift+Enter) into a 1-column range with the same number of rows that the formula above returns (it encodes the missing pairs into a single number). I entered it into D4:D8:

=SMALL(IFERROR(0>MATCH(INDEX(A2:A40,INDEX(SMALL(IF(A2:A40<>A1:A39,ROW(A2:A40)-ROW(A1)),ROW(INDIRECT("1:"&SUM(--(A2:A40<>A1:A39))))),INT((ROW(INDIRECT("1:"&ROWS(C2:C12)*SUM(--(A2:A40<>A1:A39))))-1)/ROWS(C2:C12))+1))&INDEX(C2:C12,MOD((ROW(INDIRECT("1:"&ROWS(C2:C12)*SUM(--(A2:A40<>A1:A39))))-1),ROWS(C2:C12))+1),A2:A40&B2:B40,0),INDEX(SMALL(IF(A2:A40<>A1:A39,ROW(A2:A40)-ROW(A1)),ROW(INDIRECT("1:"&SUM(--(A2:A40<>A1:A39))))),INT((ROW(INDIRECT("1:"&ROWS(C2:C12)*SUM(--(A2:A40<>A1:A39))))-1)/ROWS(C2:C12))+1)*ROWS(C2:C12)+MOD((ROW(INDIRECT("1:"&ROWS(C2:C12)*SUM(--(A2:A40<>A1:A39))))-1),ROWS(C2:C12))+1),ROW(INDIRECT("1:"&SUM(--(A2:A40<>A1:A39))*ROWS(C2:C12)-ROWS(A2:A40))))

Again, change A40 to the last cell of your data in column A and A39 to the second to last cell.

Next, enter this formula into E4 and fill down:

=INDEX($A$2:$A$40,INT(D4/ROWS($C$2:$C$12)))

Finally, enter this formula into F4 and fill down:

=INDEX($C$2:$C$12,MOD(D4,ROWS($C$2:$C$12)))