A practical way to use the IF function in Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

The IF function is a very useful logical tool. I have written an explanation on the IF Function before so I wont repeat that here. There are a number of other posts also that incorporate the use of the IF Function.

ExcelWhat I want to do is highlight a useful way to use the IF function.

I use it a lot to compare lists of data. today I had been sent some data in a spread sheet that contained email addresses. I had to create a mail out to those email addresses and when i did that I received a number of bounce backs for incorrect email addresses. I then received a second email from the original person containing a revised spread sheet with the corrections to email addresses. given that there were a large number of addresses and only a few changes I didn’t want to painstakingly work my way through the list looking for changes. Instead I copied the new list beside the old list and then wrote an if statement to compare the original with the revised. It is very simple and uses a 1 or 0 result. You could use anything there including TRUE or FALSE or “OK” and “ERROR” etc. Then a simple filter showed me the incorrect ones and the fixes i needed to apply in my mailing list.

Obviously I have obfuscated the email addresses in this example for privacy reasons.

The actual IF statement is very simple. In Cell C1 I wrote =IF(B2=A2,0,1). Filling it down is as simple as double-clicking the right hand bottom corner of the cell.

image

Then I added a simple filter to hide the correct results and only display those with differences.

image

I use this technique many times a week in comparing data lists.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Ray Blake's picture

You can do this more simply

You can do this more simply without the IF. As your formula, just have: =B2=A2. This will return either TRUE or FALSE and you can filter on that.

Ray

jethro's picture

Very true Ray. The IF

Very true Ray. The IF statement allows more flexibility later on. for instance you can extend to using MID, LEFT and RIGHT LEN and FIND functions to look for certain attributes - eg in this case find all the email addresses with Gmail.com after the @ sign.
Thanks for your comment!