Search code examples
excelexcel-formulavlookup

Excel VLOOKUP between two columns of words

I have two columns of words (Old list) and (New list) I'm trying to check using the VLOOKUP() function both columns and find which words of the New list doesn't appear in the Old list.

enter image description here

(The answers here is obviously: eyes, john, martha, phone)

I'm giving this simple example to know what VLOOKUP() formula to also apply to a larger sample. In reality I have two columns of over 1000 items. Thanks

Solution

  • =FILTER(F2:F10, ISNA(VLOOKUP(F2:F10,A2:A10,1,FALSE)))

    This will filter it down and remove all the N/A values while comparing the 2nd list to the first.

    enter image description here

    EDIT: as per comment needing clarification (too long to put as a comment)

    You can do anything you’d like by substituting references. It’s best to think of the syntax of things here to get a lay of the land and figure out what you want to pull information from.

    The ISNA() just handles N/A errors that will occur with partial lists and is irrelevant to understanding where to put your references.

    =filter(array, include, [if_empty])

    Where “array” is the range you want to filter FROM And “include” is what you are searching FOR from that range. The [if_empty] is optional – you can put a text there, like “No Results” in quotes so that it’ll substitute that for N/A.

    The “include” portion is where I’ve added additional information because I want to narrow in what I’m including…. In the plain and simple form of =filter() you’d just be putting a word/cell reference you’re looking for. It’ll pull every column for the whole table if there are multiple columns. But we want it to search multiple criteria simultaneously.

    =vlookup(lookup_value, table_array, col_index_num,range_lookup)

    what you are searching FOR (in this case anything within the table), where you want to look for it (where you are searching FROM), which column to find it in (column 1 in your case), and TRUE/FALSE – exact or approximate matches.

    The easiest way to search across multiple sheets is to have the files open, and as you type in the formula bar when you get to each section click and highlight what you want by switching sheets (alt tab). Just pay really close attention to searching FOR versus searching FROM and you can do any combination of comparison you need.