January 2015 – tecivo BLOG

tecivo BLOG

My IT notes, 'How To...' guides, Microsoft, SharePoint, Citrix, AppSense, Apple, technology & gadgets and more!

Month: January 2015

Excel Tips…Using VLOOKUP to check if cell value found in second data set

Use VLOOKUP, IF and ISNA to find whether cell value appears in comparison data set.

Example: Search for A1 value in Sheet2 Column A and if found return Found. If not found, return Not Found

=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"Not found","Found")

Sheet1

A B < B function >
1 Ford Found =IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
2 Lotus Found =IF(ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
3 BMW Found =IF(ISNA(VLOOKUP(A3,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
4 Audi Not Found =IF(ISNA(VLOOKUP(A4,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
5 Mini Found =IF(ISNA(VLOOKUP(A5,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)

Sheet2

A B C D E F G
1 Ford US Escort Red
2 Lotus UK Elise Silver
3 BMW Germany 330 Blue
4 VW Germany Golf Black
5 Ford US Mondeo Silver
6 Mini Cooper

Also see

 

Excel Tips…Using IFERROR

Introduction

IFERROR is a useful function to avoid having cells contain #N/A where a match isn’t found when doing calculations or lookups.

Use IFERROR to replace #N/A with “not found” when using VLOOKUP

Sheet1

A B < B function >
1 Ford Red =VLOOKUP(A1,Sheet2!A:D,4,FALSE)
2 BMW Blue =VLOOKUP(A2,Sheet2!A:G,4,FALSE)
3 Audi #N/A =VLOOKUP(A3,Sheet2!A:G,4,FALSE)

Sheet2

A B C D
1 Ford US Escort Red
2 Lotus UK Elise Silver
3 BMW Germany 330 Blue

Where there is no match cell contains #N/A

So to replace #N/A with specified text not found add IFERROR in front of the VLOOKUP function with the required text wrapped in quotes at the end.

Sheet1

A B < B function >
1 Ford Red =IFERROR(VLOOKUP(A1,Sheet2!A:D,4,FALSE),”not found”)
2 BMW Blue =IFERROR(VLOOKUP(A2,Sheet2!A:D,4,FALSE),”not found”)
3 Audi #not found =IFERROR(VLOOKUP(A3,Sheet2!A:D,4,FALSE),”not found”)
3 VM =IFERROR(VLOOKUP(A4,Sheet2!A:D,4,FALSE),””)

To replace #N/A with empty cell, use

=IFERROR(VLOOKUP(A4,Sheet2!A:F,4,FALSE),"")

Also see

 

© 2019 tecivo BLOG

Theme by Anders NorenUp ↑