0

Eg. I have List A and List B.

List A includes (1,2,3,4,5) List B includes (6,2,3,4,0)

I want the output which are not in the list A: 6,0 Without returning error like in vlookup.

Thanks in advance!

1
  • VLOOKUP can be used with ISERROR. Use formula in next column as =IF(ISERROR(VLOOKUP(B1,$A$1:$A$5,1,FALSE)),B1,"") Drag this to end of list. However this won't remove the unwanted blank rows in output column that you can filter and manually remove or it could be automated using Excel macros.
    – patkim
    Jul 3, 2015 at 18:44

2 Answers 2

1

With data in columns A and B, select C1 through C5 and enter the array formula:

=IF(ISERROR(MATCH(B1:B5,A1:A5,0)),B1:B5,"")

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Note the braces that appear in the Formula Bar.

Reference:

Chip Pearson

0

Very short answer:
=IFERROR(VLOOKUP(...),"NOT IN LIST")


The more complete answer:

    --- A ---   --- B ---       --- C ---       --- D ---   --- E ---
[1] A, database B, lookup val   Vlookup()       Match()     Cell()
[2] 1           6               NOT IN LIST     -1          NOT FOUND
[3] 2           2               2               2           $A$3
[4] 3           3               3               3           $A$4
[5] 4           4               4               4           $A$5
[6] 5           0               NOT IN LIST     -1          NOT FOUND

The use of IFERROR as in =IFERROR(VLOOKUP(B2;$A$2:$A$6;1;0);"NOT IN LIST") (example from cell C2) will display "NOT IN LIST" for any error from VLOOKUP().

The same lookup can be done with MATCH(), with the difference that the return will be an index into the list ("1" for the first cell); example in column D above; D2 contains =IFERROR(MATCH(B2;$A$2:$A$6;0);-1) where the last -1 is displayed for an error (e.g. 'not found').

The result from column D can then be further processed to get the cell address, as in column E; E2 contains =IF(D2>0;CELL("address";OFFSET($A$2;D2-1;0));"NOT FOUND") - when this isn't NOT FOUND an INDIRECT(D2) will return the value from the cell in column A (as pointed to by the reference).

If you prefer to have a list with just the sought after values, then use =IF(ISNA(VLOOKUP(B2;$A$2:$A$6;1;0));B2;"") with use of VLOOKUP() or =IF(ISNA(MATCH(B2;$A$2:$A$6;0));B2;"") for using MATCH() - you must enter these on line 2 as they are written here . These are included in columns F and G in the CSV file below.

Copy the CSV text into Notepad (not Wordpad!) or any other text editor. Then save it into a file, make the name of the file end in .csv. The file should now open just fine with your spreadsheet (Excel, LibreOffice-Calc, ...) - it should work in any of them.


CSV file for the above:

"A, database","B, lookup val","'Vlookup()","'Match()","Cell()","'REV Vlookup()","'REV Match()"
1,6,"=IFERROR(VLOOKUP(B2;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B2;$A$2:$A$6;0);-1)","=IF(D2>0;CELL(""address"";OFFSET($A$2;D2-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B2;$A$2:$A$6;1;0));B2;"""")","=IF(ISNA(MATCH(B2;$A$2:$A$6;0));B2;"""")"
2,2,"=IFERROR(VLOOKUP(B3;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B3;$A$2:$A$6;0);-1)","=IF(D3>0;CELL(""address"";OFFSET($A$2;D3-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B3;$A$2:$A$6;1;0));B3;"""")","=IF(ISNA(MATCH(B3;$A$2:$A$6;0));B3;"""")"
3,3,"=IFERROR(VLOOKUP(B4;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B4;$A$2:$A$6;0);-1)","=IF(D4>0;CELL(""address"";OFFSET($A$2;D4-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B4;$A$2:$A$6;1;0));B4;"""")","=IF(ISNA(MATCH(B4;$A$2:$A$6;0));B4;"""")"
4,4,"=IFERROR(VLOOKUP(B5;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B5;$A$2:$A$6;0);-1)","=IF(D5>0;CELL(""address"";OFFSET($A$2;D5-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B5;$A$2:$A$6;1;0));B5;"""")","=IF(ISNA(MATCH(B5;$A$2:$A$6;0));B5;"""")"
5,0,"=IFERROR(VLOOKUP(B6;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B6;$A$2:$A$6;0);-1)","=IF(D6>0;CELL(""address"";OFFSET($A$2;D6-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B6;$A$2:$A$6;1;0));B6;"""")","=IF(ISNA(MATCH(B6;$A$2:$A$6;0));B6;"""")"
9
  • 1
    This hint gets you half way to the requested solution. Can you make the answer less short and run it all the way home?
    – fixer1234
    Jul 3, 2015 at 19:31
  • Please, give a complete answer, not just a stab at it. Jul 4, 2015 at 6:58
  • Well, then; an attempt to add some more words to it...
    – Hannu
    Jul 4, 2015 at 7:28
  • This gives the opposite of what the question asks. The OP is looking for just the 6 and 0 as the output.
    – fixer1234
    Jul 4, 2015 at 15:20
  • @fixer1234 well, that depends on how you interpret I want the output which are not in the list - Set an Autofilter on the table above and filter on NOT IN LIST - done. Really; Now choose your favourite.
    – Hannu
    Jul 4, 2015 at 16:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .