﻿﻿﻿﻿ January 2015 – tecivo BLOG

## tecivo BLOG

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

#### Month: January 2015

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

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

#### Sheet1

A B < B function >

#### 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

## 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 >
`=IFERROR(VLOOKUP(A4,Sheet2!A:F,4,FALSE),"")`