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

 

 

 

 

 

 

 

 

 

 

 

Leave a Reply