tecivo BLOG

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

Tag: VLOOKUP (page 2 of 3)

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

 

Excel Tips…Using VLOOKUP to return custom text for match/no match found

See Excel Tips…Using VLOOKUP for introduction to VLOOKUP

————

Rather than return #NA when match isn’t found, use IFERROR with custom text to use when there is no cell match

Sheet1

A B < B function >
1 Ford Red =IFERROR(VLOOKUP(A1,Sheet2!A:F,4,FALSE),”not found”)
2 Lotus Silver =IFERROR(VLOOKUP(A2,Sheet2!A:F,4,FALSE),”not found”)
3 BMW Blue =IFERROR(VLOOKUP(A3,Sheet2!A:F,4,FALSE),”not found”)
4 Audi not found =IFERROR(VLOOKUP(A4,Sheet2!A:F,4,FALSE),”not found”)
5 Mini 0 =IFERROR(VLOOKUP(A5,Sheet2!A:F,4,FALSE),”not 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

Note: First match is used so Ford in Sheet2 row 1 is used – Ford in Sheet2 row 5 is ignored

To use custom text for #NA and 0, use IFERROR, IF and ISBLANK with custom text

A B < B function >
1 Ford Red =IFERROR(IF(ISBLANK(VLOOKUP(A1,Sheet2!A:F,4,FALSE),”no data”,VLOOKUP(A1,Sheet2!A:F,4,FALSE)),”not found”))
2 Lotus Silver =IFERROR(IF(ISBLANK(VLOOKUP(A2,Sheet2!A:F,4,FALSE),”no data”,VLOOKUP(A2,Sheet2!A:F,4,FALSE)),”not found”))
3 BMW Blue =IFERROR(IF(ISBLANK(VLOOKUP(A3,Sheet2!A:F,4,FALSE),”no data”,VLOOKUP(A3,Sheet2!A:F,4,FALSE)),”not found”))
4 Audi not found =IFERROR(IF(ISBLANK(VLOOKUP(A4,Sheet2!A:F,4,FALSE),”no data”,VLOOKUP(A4,Sheet2!A:F,4,FALSE)),”not found”))
5 Mini no data =IFERROR(IF(ISBLANK(VLOOKUP(A5,Sheet2!A:F,4,FALSE),”no data”,VLOOKUP(A5,Sheet2!A:F,4,FALSE)),”not found”))

As shown in row 6, replace “not found” with “” for no text

You can also use a Custom Cell format to suppress the 0

Also see

 

Older posts Newer posts

© 2017 tecivo BLOG

Theme by Anders NorenUp ↑