How To Use IF Function Instead Of VLOOKUP

How To Use IF Function Instead Of VLOOKUP

Lookup function is an important part of any Spreadsheet program, especially in Excel. Normally, whenever a Lookup requirement comes in Excel we think about VLOOKUP, INDEX- MATCH & XLOOKUP function etc, but in some situation IF function can be a replacement of VLOOKUP.

To compare two Worksheets or two Workbooks data normally we use VLOOKUP. Refer following example with picture.

IF as a replacement of VLOOKUP

In above example there are two set of data, i.e. Week1 & Week2. In this example we want to compare both set of data to know the number of mismatched employee (that is the new employee). I have added a check point column in second set of data(Week2) and plotted normal VLOOKUP function where #NA means it’s a new employee that is not available in Week1 data. Above job can be done by IF with COUNTIF function also. I have used two data array on the same sheet but similar way we can use same function for two Worksheets or Workbooks.

Refer following example with picture to understand this in detail.

How to use IF function in VLOOKUP in Excel

In above example there are again two set of data, i.e. Week1 & Week2 where we want to compare both set of data to know mismatched employee that is the new employee. I have added a check point column in second set of data(Week2) and used IF with COUNTIF function where #NA means its a new employee which is not available in Week1 data. . I have used two data array on the same sheet and in similar way we can use same function for two Worksheets or Workbooks.

Note- IF is not VLOOKUP alternative in all cases. For further detail regarding VLOOKUP, IF & COUNTIF function refer to my Download page or my e-book “How to win Excel”.

 

 

 

 

 

 

 

 

 

 

 

This Post Has 5 Comments

  1. Mandy

    What’s up, just wanted to tell you, I loved this post.
    It was practical. Keep on posting!

    1. Prof RASHID RIZWI

      Thank you

  2. Landon

    Hey there! Someone in my Myspace group shared this website with us so I came to check it out.

    I’m definitely loving the information. I’m bookmarking and will be tweeting this to my
    followers! Terrific blog and great design and style.

    1. Prof RASHID RIZWI

      Thank you so much

  3. Carrol

    Hello there! Do you use Twitter? I’d like to follow you if that would be okay.
    I’m definitely enjoying your blog and look forward to
    new updates.

Leave a Reply