People compare lists (or columns) of information all the time, and Excel provides several easy ways to do it. Microsoft sums up several real-life examples quite nicely:
For financial record-keeping, you often have to compare two lists to find data that appears on one but not on the other. Which new clients need to be added to the master database? Which credit card receipts are not reflected on the monthly statement, and vice versa? What checks and deposits are still outstanding? It’s a tedious task if you do it manually.
In the example below, we have two large lists of four digit account values: List A and List B. We want to know if any account values from List A are missing from List B, and vice versa. To do this we’ll write a formula using the ISNA and MATCH functions.
How it’s done
- Arrange the lists in columns, leaving at least two blank columns in between. I chose column A for List A and column D for List B.
- Label the columns next to each list “Missing?” In this example, columns B and E are each labeled “Missing?”
- In cell B5 enter the formula:
=ISNA(MATCH(A5,D:D,0))and press Enter.
- Select cell B5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Text that Contains…
- In the Text That Contains dialogue box, type TRUE and click OK.
- Select cell B5 down to the end of List A and press Ctrl+D to copy the formula and conditional formatting down the entire List A.
- In cell E5, enter the formula:
=ISNA(MATCH(D5,A:A,0))and press Enter. - Repeat steps four through six for cell E5, making sure to copy the formula down the entire List B.
The result
If any value from List A is missing from List B, a red TRUE will appear next to that value in List A’s “Missing?” column. If any value from List A is found in List B, FALSE will appear next to that value in List A’s “Missing?” column.
TRUE = value is missing from the other list
FALSE = value is not missing from the other list
You could take it one step farther by filtering either list to display only TRUE which would then only show the values missing from either list.
Done and done.
How and why it works
ISNA is the first function in our formula, which does only one thing in life: checks whether a value is #N/A (which is the error value Excel uses to say “no value is available”). If a value is #N/A in an ISNA formula, it’ll display TRUE. Otherwise, it’ll display FALSE.
MATCH is the second function, which returns the relative position of an item in an array (in our example, either List A or List B) that matches a specified value in a specified order. In our example we are using MATCH to tell ISNA whether to display TRUE or FALSE.
If MATCH finds a value from List A within List B, it’ll return the relative position of the value in List B. Whenever MATCH returns the relative position and not #N/A, ISNA simply spits out FALSE. If MATCH does not find a value from List A within List B, it’ll return #N/A and ISNA will display TRUE.
The conditional formatting just makes it easier to spot the missing values.
The formula
=ISNA(MATCH(what,where,type))
MATCH has three required arguments, or inputs, to work:
lookup_value(what do you want Excel to lookup or find)lookup_array(where should Excel look. In our example, A:A and D:D selects those entire columns)match_type(what type of a match Excel should find: the next largest, an exact match only, or the next smallest)
ISNA literally checks whether the cell value “IS #N/A,” or “IS NO VALUE AVAILABLE?”
- If it IS #N/A, it’ll display TRUE.
- If it IS NOT #N/A, it’ll display FALSE.
Practice yourself
The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!
40 responses to “Compare two or more lists in Excel with ISNA and MATCH”
“done and done” it is. Thanks it worked like a charm. The only thing is it didn’t match the names that had spaces in front and I did that manually. Other than that it is perfect. Thanks.
Great, glad it worked out for you!
And thanks for pointing out that issue with spaces in your data. In the future you can use Excel’s
=TRIM()function to remove leading and trailing spaces from text. In the example from the post, just update the formulas like this:=ISNA(MATCH(TRIM(A5),D:D,0)), and=ISNA(MATCH(TRIM(D5),A:A,0)), then copy the formulas down to the ends of each list. This will eliminate the need for any manual adjustments!how to get result in same raw , I mean result of look up value should display in a same raw in lookup array.
Do you know of a way to make the match_type formula do a “fuzzy” search for text such as names (Last, Firt and/or Last, and/or First Last, etc)?
Sorry for the terribly delayed response!
Unfortunately Excel’s
MATCHfunction only works for exact matches, however Microsoft does provide a Fuzzy Lookup Add-In for Excel, which may help accomplish what you’re trying to do.Is there any way for the formula to populate the opposite value. For example, if ISNA(MATCH is returning a FALSE or TRUE, can I change that to return a TRUE or FALSE?
Absolutely! All you need to do is modify the formula to include an
IF function. For example, instead of=ISNA(MATCH(D5,A:A,0), you would use=IF(ISNA(MATCH(D5,A:A,0)),"FALSE","TRUE")The
IF functiontakes three arguments (or inputs) to work:=IF(logical_test, [value_if_true], [value_if_false])ISNA(MATCHpart of the formula, which can only returnTRUEorFALSEISNA(MATCHreturnsTRUE. In your case, you want it to display the wordFALSE, which is done using “double quotes”.ISNA(MATCHreturnsFALSE. In your case, you want it to display the wordTRUE, which is done using “double quotes”.Make sense?
For some reason I really can’t get this to work. Does it not work if you leave every few rows blank? I’ve done everything like in the instructions but then the formulas just sit there and don’t change to true and false. Argh I really need to figure this out but I’m totally stuck! Please help!
When you say the “formulas just sit there and don’t change to true and false,” do you mean that the cell displays the formula (e.g.
=ISNA(MATCH(D5,A:A,0)) instead of the resultTRUEorFALSE? If so, navigate to Formulas → Show Formulas and deselect (or uncheck) Show Formulas. If that does not work, make sure that the cells containing the formulas are not accidentally formatted as Text.If neither of these solve your problem, try downloading the example file I provided to see where your formulas differ from mine.
Try changing commas in the formula to semicolons. That worked for me.
Thank you so much for sharing your knowledge and explaining how it works. Very helpful!
You’re very welcome! Happy to help!
This is almost what I am looking for. I have 5 columns of text, with some cells blank. I need to compare each row to see if the text matches or not, ignoring the blank cells. Is there a way to do this?
You could highlight the row, then click on the Home tab in Excel 2010, click Conditional Formatting, select Highlight Cells Rules and finally select Duplicate Values. Excel will highlight the duplicate values, or the “matches,” and ignore the blanks. Then you could copy that row, and paste special as “formatting” to the remaining rows. Would that work for you?
What if I don’t want to trim A5 or D5?
What if I want to trim D:D and A:A?
I tried =IF(ISNA(MATCH(D5,TRIM(A:A),0)),”FALSE”,”TRUE”)
however this appears to return the wrong true or false value.
Unfortunatley the fuzzy excel add-in is also not an option for me.
When you copy the two formulas I provided,
=ISNA(MATCH(TRIM(A5),D:D,0))and=ISNA(MATCH(TRIM(D5),A:A,0)), and paste them down to the ends of your lists, the value within theTRIMfunction will automatically update to the correct cell for that row (e.g., the next row’s formulas would be:=ISNA(MATCH(TRIM(A6),D:D,0))and=ISNA(MATCH(TRIM(D6),A:A,0))).I believe this formula already accomplishes what you’re after. I assume by saying you want to trim D:D and A:A, you mean you want to trim each cell in column D and A, respectively. This formula will trim a cell in each row for which you copy and paste the formula. If you want to trim each cell in your list, just copy this formula down the entire list.
How to make this work when i have text in the cells more than 255 characters?
Hi, could you assist. I want to do a lookup formula. My problem though is as follows:-
It has to look up on EITHER/OR criteria. In other words if either the invoice number in column A matches Column A in another sheet or If the stock number in Column B matches column B in the other spreadsheet. I then want it to then return what is in 3rd column which is the department. Vlookup doesn’t work with OR statement. I have tried IF match and index and cant seem to get it right.
Would really appreciate some help
Hi Deb, I’m back after a long hiatus from writing – did you ever figure out how to solve your problem? If so, would you mind sharing (that is if you can remember!)?
Hi Ryan,
Another way of comparing two lists, is doing two vlookups:
Vlookup #1: Looks up individual line items from List A in List B. Error Values mean “item in List A but not in List B”
Vlookup #2: Looks up individual line items from List B in List A. Error Values mean “items in List B but not in List A”
I use this very often. Just quickly do the vlookups and then filter cells with error values. Though this method does get cumbersome when you are comparing more than two lists.
I know this response is to a comment you left nearly five years ago, but I wanted to say thank you for the great tip! You’re right, this is another quick and easy way to compare two lists, and while it works very similarly to
ISNAandMATCH, your method might be a little easier for those more familiar with usingVLOOKUP.Hi, I’m hoping someone here might have the answer. My problem is that I have two lists (two tabs in one workbook)
one list of : forenames + surnames + address, and one list of
forenames + surnames + emails,
Does anyone know if there is a way to get excel to match “forename” + “surname” in list A = “forename” + “surname” in list B and enter the value(email) from the adjacent cell in list B, into the adjacent cell in list A
Any advice would be gratefully appreciated.
see the below link please
http://www.excel-easy.com/examples/two-column-lookup.html
adels
I would probably do it in a few steps, just to be sure I didn’t confuse the data. You can use the formula above with “ISNA” and “Match”, and then in another column, use the “If” and “Vlookup” functions. So, if your formula comes out “True”, you can do an “=if(*cell*=TRUE,vlookup(……..),””). This can pull the email from the one list and add it to a column near the other. Just hide the columns with the formulas so they don’t display if the data isn’t relevant.
Prior to doing all this, you may want to combine the forenames and surnames into one cell. You can do this easily with “=cell1&cell2”
If you want a space between them, simply use “=cell1&” “&cell2”
Combining them can help if you have multiple first or last names, e.g. several “Johns” or “Smiths”
Hi… I need a help.. can you help me..
I have a two excel sheet in that column A : First Name column B Second and C last Name and d is net pay amount… in other hand I have column A first name, second name and last name in combined format, Column B net pay.. in this I have to compare the name and net pay… how should I go about it..
I am wondering if there is a way to make the exact, work with spaces. For instance, if we are looking for a match to “ape” and our list contains “grape” then a match is found. So how can we (without changing the data) ask the formula to add a space before and after the lookup data to prevent this. Or is there another way?
For instance, using my example above, I want the actual lookup data to be appended with a beginning and ending space so that “Ape” is turned into ” Ape ” to prevent a return from the array when “grape” is found.
FYI, I already tried change the A1 (lookup data) to ” “&A1&” ” but that does not seem to work.
If I have to compare more than 2 columns? Like say 20. Please help me
Hello Ryan, I read and re-read the wonderfully instructive guides you so kindly provided the community and, I thank you for that. However, for the life of me, I can’t find a way to perform a lookup and/or match for two sets of columns (i.e. numbers) in which column A may match some of column B’s but column B may not have column A’s numbers. Each item number has a cost attached. The goal is to be able to compile a total listing of ALL numbers in one column with a calculation of cost between A and B or simply list A’s cost if no match in column B or vice versa. I hope this layman’s description is enough to give you a clear sense of my goal. Below is a visual sample – hope it helps. Thanks for your time and your anticipated assistance. Greatfully, J.M. ItemA Cost A ItemB Cost B Goal intended
00077 $2,102.01 00077 $2,649.15 00077 $547.14
00089 $376.12 00092 $65.31 00089 $376.12
00112 $284.16 00144 $272.52 00092 $65.31
00151 $371.37 00191 $399.70 00112 $284.16
00191 $509.60 00192 $644.30 00144 $272.52
00192 $660.92 00196 $123.87 00151 $371.37
00196 $415.34 00213 $1,691.63 00191 $(109.90)
Hi There,
Above explained only the way to compare data between 2 columns. what if I want to compare more than 2 columns, for ex- I want to compare values(can be Number or String) in Column ‘B’ to the values in Column- C,C,E,F,G..so on, and want to see results only once i.e if all values Matches or not. Below is the example of data I’m using all in same sheet. Please help on same.
PA_ALIAS PA_VALUE OM1 Value-Om2 Value-OM3 Value-OM4
AccessibleEnhanced FALSE FALSE FALSE FALSE
ActivityStatusUpdateEmailAddr String String String String
ActuateConnStr CHANGE_ME CHANGE_ME CHANGE_ME CHANGE_ME
ActuateReportCastDomain CHANGE_ME CHANGE_ME CHANGE_ME CHANGE_ME
ActuateReportCastHost CHANGE_ME CHANGE_ME CHANGE_ME CHANGE_ME
ActuateReportCastLang ENU ENU ENU ENU
ActuateReportServerHost CHANGE_ME CHANGE_ME CHANGE_ME CHANGE_ME
ActuateServerEnabled FALSE FALSE FALSE FALSE
AllowAnonUsers TRUE TRUE TRUE TRUE
ABC ABCMMM = MMM
AC ACDV = DV
BGR BGRTT = TT
get differnt text only
Thank you, Ryan. Being a marketer who is not very technical, your instructions were simple and useful. Much appreciated!
Great Maria, glad you found it helpful!
Yes, this works fine for numbers. I was looking for a method to identify matches within multiple columns in TEXT format.
I thought I was really close with this suggestion but I suspect my data elements are being listed incorrectly.
What I am doing is taking a serial # listed in a column on tab “1” and looking down a list of Serial numbers on a separate spreadsheet on tab “2” to see if any of the #’s are on both sheets. If listed on the second sheet, I’d like to post a small word or message in the cell on page one to signify serial number is this special kind I have on page 2.
So far all I come up with is Excel telling me that it is over there but when I do a Find on Tab 2…the serial number never shows up. I think I am making this harder than I have to. Ultimately if the number is not on both sheets, I want to leave a blank space. How hard can this be?
This is great – thanks. Is there a way I can get it to look at several same numbers at a time and highlight any that are not there. For instance I may have 4 x 500’s in list A and 3 x 500’s in list B. Can it highlight the fact that one 500 is not matched between the 2 lists? Hope that makes sense! Thanks
Hi,
Is there any way to compare multiple columns in excel, i have column A-H with various headers.
There are are product features lists 125 features.
Its very similar to websites which compare 4-5 products side by side.
Regards,
Kumar
how about a text and number format (e.g. aa 0001) what is the formula
I have 2 tables created in the excel..i need to lookup for the application IDs in the 1 first table,if the application id is available then the value should return as “Yes” and the execution should stop here if the application ID is not available in the table 1 then i need to lookup in the second table, if the application id is available here then it should return “yes” if not “No”. Could someone help with the formula for the same?
I have two tabs on Excel. Tab 1 has a list of donors and the amount they donated during the year (Col 1 is last name, col 2 is first name, col 3 is donation amount). Tab 2 has a list of all donor names and addresses. Is there any way I can find the addresses on tab 2 to match with the list of donors on tab 1?