People compare lists (or columns) of information all the time, and Excel provides several easy ways to it. In fact 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 to Do It

- Arrange the lists in columns, leaving at least two blank column in between. I chose column A for List A and column D for List B.
- Name 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 dialog 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 4-6 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.

A TRUE = value is missing from the other list

A 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 display 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 (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!

AB“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.

Ryan DohertyGreat, 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!patelhow to get result in same raw , I mean result of look up value should display in a same raw in lookup array.

Joan B.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)?

Ryan DohertySorry for the terribly delayed response!

Unfortunately Excel’s

`MATCH`

function 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.Jeff KohlIs 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?

Ryan DohertyAbsolutely! 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 function`

takes three arguments (or inputs) to work:`=IF(`

logical_test,[value_if_true],[value_if_false])logical_testwould be the`ISNA(MATCH`

part of the formula, which can only return`TRUE`

or`FALSE`

[value_if_true]is the value you want to display if`ISNA(MATCH`

returns`TRUE`

. In your case, you want it to display the word`FALSE`

, which is done using “double quotes”.[value_if_false]is the value you want to display if`ISNA(MATCH`

returns`FALSE`

. In your case, you want it to display the word`TRUE`

, which is done using “double quotes”.Make sense?

pineappleFor 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!

Ryan DohertyWhen 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 result`TRUE`

or`FALSE`

? 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.

JonnaTry changing commas in the formula to semicolons. That worked for me.

ElizabethThank you so much for sharing your knowledge and explaining how it works. Very helpful!

Ryan DohertyYou’re very welcome! Happy to help!

SteveThis 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?

Ryan DohertyYou 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?

SteveWhat 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.

Ryan DohertyWhen 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 the`TRIM`

function will automatically update to the correct cell for that row (e.g., the next row’s formulas would be:`=ISNA(MATCH(TRIM(`

andA6),D:D,0))`=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.

RamHow to make this work when i have text in the cells more than 255 characters?

DebHi, 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

09755Hi 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.

KizzHi, 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.

adelsee the below link please

http://www.excel-easy.com/examples/two-column-lookup.html

adels

JoeI 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”

jananiHi… 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..

GEMAffair.comI 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.

GEMAffair.comFYI, I already tried change the A1 (lookup data) to ” “&A1&” ” but that does not seem to work.

Arnit DaveIf I have to compare more than 2 columns? Like say 20. Please help me

J.M.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)

SatyaHi 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

radhaABC ABCMMM = MMM

AC ACDV = DV

BGR BGRTT = TT

get differnt text only

Maria SyedThank you, Ryan. Being a marketer who is not very technical, your instructions were simple and useful. Much appreciated!

Ryan DohertyGreat Maria, glad you found it helpful!

Joan BuddenhagenYes, this works fine for numbers. I was looking for a method to identify matches within multiple columns in TEXT format.

Steve PassehlI 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?

simonThis 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

RahulHi,

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

jerboyhow about a text and number format (e.g. aa 0001) what is the formula

Esskayb4uI 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?

TimI 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?