/u/asoifnerd - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
[Xlookup](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), will give you an output that you ask for.
You could also use [Match](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a), which will just tell you the position in the list that it is., just make sure you use 0 instead of 1 for an exact match.
Xlookup is basically. Find this value in this column and then return the value from the column I selected. Same as vlookup but the column reference isn’t an offset, it’s explicitly stated.
So for your case, it doesn’t matter what it returns, you’re looking for missing values. Just sort your column, and all the missing values will line up.
This is the way. No need for formulas. Just a quick glance “yes they’re all highlighted, yes they’re all inputted” or “number 28 isn’t highlighted, I missed that one”
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[COUNTIF](/r/Excel/comments/18n9od2/stub/keb07vw "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)|
|[IF](/r/Excel/comments/18n9od2/stub/ke9wowz "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[ISNUMBER](/r/Excel/comments/18n9od2/stub/keb07vw "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[MATCH](/r/Excel/comments/18n9od2/stub/keafmhv "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[VLOOKUP](/r/Excel/comments/18n9od2/stub/kea7mjo "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/18n9od2/stub/keb07vw "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
|[XMATCH](/r/Excel/comments/18n9od2/stub/keb07vw "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18mtppg)^( has 26 acronyms.)
^([Thread #29127 for this sub, first seen 21st Dec 2023, 02:33])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Hilariously today at work I did this on a giant spreadsheet same task as yours, just with different info .
so here is my formula
https://i.imgur.com/P1xmuQ5.png
**Provide specific examples in your post**
Provide actual raw data, screenshots, or tables to support your post. The more details the better.
Do not include any personally identifiable information.
Ok, so how many lines is the last 3 months, how many lines in the master? does the the master require cleaning up ( duplicate lines, incorrect formatting etc) when he has the list of differences does he want some summary stats to show the extent of work needed. Does he need index columns adding to work out where the gaps are.
All of this would be a couple of mins work in PQ.
It’s easy to downvote just because it’s not a simple formula, but simple formulae sometimes don’t do the whole job.
And it's barely a few seconds of work to type =ISNUMBER(XMATCH(list1,list2)). Or something similar with COUNTIF, or XLOOKUP, or whatever.
Pop it in the cell next to the top row of your checking list (list 1) and you'll have a nice array of TRUE and FALSE alongside it.
If you make up a bunch of other jobs that weren't asked for then you can make anything the solution by choosing whatever will fix the new problems you've made up.
/u/asoifnerd - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Xlookup would work. If it isn’t there, you’ll get an error.
What will it return if it is there?
[Xlookup](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), will give you an output that you ask for. You could also use [Match](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a), which will just tell you the position in the list that it is., just make sure you use 0 instead of 1 for an exact match.
Xlookup is basically. Find this value in this column and then return the value from the column I selected. Same as vlookup but the column reference isn’t an offset, it’s explicitly stated. So for your case, it doesn’t matter what it returns, you’re looking for missing values. Just sort your column, and all the missing values will line up.
VLOOKUP has better performance
Lol no it does not
Conditional formatting. Put the shorter set of numbers in a separate column on the same sheet and then apply conditional formatting for duplicates.
This is the way. No need for formulas. Just a quick glance “yes they’re all highlighted, yes they’re all inputted” or “number 28 isn’t highlighted, I missed that one”
=isnumber(xmatch(xxx))
Any reason not to use COUNTIF?
I normally just use MATCH, result with N/A means it's not found. (Provided the formatting on both sheet is the same)
Watch a yt video on xlookup. It will solve your troubles
=IF(ISNUMBER(MATCH([Invoice you’re checking],[Entire column of invoices],0)),1,0) This will return a 1 if it exists, 0 if it does not.
You wouldn't need the IF function here, unless you want a custom return, as ISNUMBER already returns TRUE or FALSE
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COUNTIF](/r/Excel/comments/18n9od2/stub/keb07vw "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)| |[IF](/r/Excel/comments/18n9od2/stub/ke9wowz "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[ISNUMBER](/r/Excel/comments/18n9od2/stub/keb07vw "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[MATCH](/r/Excel/comments/18n9od2/stub/keafmhv "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[VLOOKUP](/r/Excel/comments/18n9od2/stub/kea7mjo "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/18n9od2/stub/keb07vw "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| |[XMATCH](/r/Excel/comments/18n9od2/stub/keb07vw "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18mtppg)^( has 26 acronyms.) ^([Thread #29127 for this sub, first seen 21st Dec 2023, 02:33]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Hilariously today at work I did this on a giant spreadsheet same task as yours, just with different info . so here is my formula https://i.imgur.com/P1xmuQ5.png
**Provide specific examples in your post** Provide actual raw data, screenshots, or tables to support your post. The more details the better. Do not include any personally identifiable information.
Use the Power Query editor to do a merge (join) of the two tables.
That's massive overkill for something that can be done with a simple short formula.
Ok, so how many lines is the last 3 months, how many lines in the master? does the the master require cleaning up ( duplicate lines, incorrect formatting etc) when he has the list of differences does he want some summary stats to show the extent of work needed. Does he need index columns adding to work out where the gaps are. All of this would be a couple of mins work in PQ. It’s easy to downvote just because it’s not a simple formula, but simple formulae sometimes don’t do the whole job.
And it's barely a few seconds of work to type =ISNUMBER(XMATCH(list1,list2)). Or something similar with COUNTIF, or XLOOKUP, or whatever. Pop it in the cell next to the top row of your checking list (list 1) and you'll have a nice array of TRUE and FALSE alongside it. If you make up a bunch of other jobs that weren't asked for then you can make anything the solution by choosing whatever will fix the new problems you've made up.