0

I have two lists: A 1 2 3 4 5 6 7

B 5 7 I want output C 1 2 3 4 6 in Excell ?? Any ideas?? Regullarly updating if i add 1 in b it should be removed from output table.

https://i.sstatic.net/lm37S.jpg https://i.sstatic.net/qjc0s.jpg

Ravi Garg
  • 1

2 Answers2

1

This needs one Helper Column & combination of INDEX and MATCH, wrapped with IFERROR:

enter image description here

How it works:

  • Formula in cell P41:

    =IF(ISNA(VLOOKUP(M41,$N$41:$N$423,1,FALSE)),"Yes","")

  • An array (CSE) formula in cell O41:

    {=IFERROR(INDEX($M$41:$M$47, SMALL(IF(COUNTIF($P$41, $P$41:$P$47)*COUNTIF($P$41:$P$47,"<>"), ROW($M$41:$N$47)-MIN(ROW($M$41:$N$47))+1), ROW(A1)), COLUMN(A1)),"")}

N.B.

  • Finish an array formula with Ctrl+Shift+Enter & fill down.

  • For neatness later on you may hide Helper Column.

  • List in column O will change, as soon you add or even delete value in column N.

  • Adjust cell references in the formula as needed.

Rajesh Sinha
  • 9,403
1

You could use:

=UNIQUE(IF(ISERROR(VLOOKUP(UNIQUE(B1:B20,0,TRUE),UNIQUE(A1:A20,0,TRUE),1,FALSE)),UNIQUE(B1:B20,0,TRUE),""),0,1)

instead.

Jeorje
  • 11