VLOOKUP – tecivo BLOG

tecivo BLOG

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

Tag: VLOOKUP (page 1 of 3)

Excel Tips…Use custom cell format to hide the zero returned when VLOOKUP source cell is empty

15th February 2017 / / 0 Comments

If you do a VLOOKUP to a reference cell which is empty, the VLOOKUP will return 0

To stop this you can apply a custom number format to the cell(s):

  • Select the cell(s)
  • from the Home tab select Format Cells
  • Select Number and Custom format
  • Enter custom number type : 0;;;@

 

Note: Whatever you put after the @ will be appended to the cell content

See https://support.office.com/en-gb/article/Display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03

Also see

 

Excel Tips…Use Conditional Formatting with VLOOKUP to colour cell when match found

7th May 2016 / / 0 Comments

Introduction

Using VLOOKUP, NOT & ISNA in a Conditional Formatting rule is a really useful way to show whether a cell’s contents appear in a second data set.

To highlight Sheet1 Column A cells that appear in Sheet2 Column A, select Sheet1 Column A and create a new Conditional Formatting rule using ‘Use a formula to determine which cells to format‘ and use formula:

=NOT(ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE)))

Sheet1

A B
1 Ford
2 Lotus
3 BMW
4 Audi
5 Mini
6 Ford

Sheet2

A B C D
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

To apply format rule to show non-matches use

=ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE))
A B
1 Ford
2 Lotus
3 BMW
4 Audi
5 Mini
6 Ford

Also see

 

Excel Tips…Using VLOOKUP and & to CONCATENATE cells before returning custom text for match/no match found

19th February 2015 / / 0 Comments

When using VLOOKUP with data that isn’t made up of unique values you need to combine values to create a unique reference

You can either create additional columns in both data sources and populate with & or CONCATENATE, or you can combine the cell grouping into the VLOOKUP function.

Example – combine Sheet1 columns A and B  to create a unique reference, then use VLOOKUP to search for same in Sheet2 (where an additional column has been created and populated with & or CONCATENATE as appropriate)

=IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),"not found","found")

Sheet1

A B C < C function >
1 Ford Escort found =IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
2 Lotus Elise found =IF(ISNA(VLOOKUP(A2&B2,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
3 BMW 330 found =IF(ISNA(VLOOKUP(A3&B3,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
4 Audi not found =IF(ISNA(VLOOKUP(A4&B4,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
5 Mini Cooper found =IF(ISNA(VLOOKUP(A5&B5,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
6 Ford Focus not found =IF(ISNA(VLOOKUP(A6&B6,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)

Sheet2

A B C D E < A function >
1 FordEscort Ford US Escort Red =B1&D1
2 LotusElise Lotus UK Elise Silver =B2&D2
3 BMW330 BMW Germany 330 Blue =B3&D3
4 VWGolf VW Germany Golf Black =B4&D4
5 FordMondeo Ford US Mondeo Silver =B5&D5
6 MiniCooper Mini Cooper =B6&D6

Also see

 

« Older posts

© 2020 tecivo BLOG

Theme by Anders Noren — Up ↑