December 2014 – tecivo BLOG

tecivo BLOG

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

Month: December 2014 (page 1 of 2)

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

 

Excel Tips…Using VLOOKUP

For me, aside from all the common features that Excel has, VLOOKUP is my single most used function.

When you need to carry out a lot of data consolidation for analysis where there are lots of different data sources to collate and compare, VLOOKUP is an essential tool.

VLOOKUP is far easier to use than it first appears, and can not only save lots of time but more importantly will remove any potential for human error when trying to compare data manually.

Typically you’ll use VLOOKUP across data held in separate worksheets or workbooks, but the method is the same when using for data in the same worksheet.

Summary:

Looks for match of source cell in target range left most column and returns value in the same row.

Arguments:

=VLOOKUP(lookup_value,table_array,column_index_number,FALSE)

e.g.

=VLOOKUP(A1,sheetname!A:C,3,FALSE)

Find match for cell A1 in sheetname Column A and return value in same row column C

Counting from column A, column C=3 – count number of columns from where match is and use this value.

FALSE means that only exact matches are returned.

Sheet1

A B < B function >
1 Ford Red =VLOOKUP(A1,Sheet2!A:G,4,FALSE)
2 Lotus Silver =VLOOKUP(A2,Sheet2!A:G,4,FALSE)
3 BMW Blue =VLOOKUP(A3,Sheet2!A:G,4,FALSE)
4 Audi #N/A =VLOOKUP(A4,Sheet2!A:G,4,FALSE)
5 Mini 0 =VLOOKUP(A5,Sheet2!A:G,4,FALSE)

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

No match found returns #N/A

If match found, but the referenced cell is empty 0 is returned.

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

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

Also see

 

Excel Tips…Use ‘&’ or CONCATENATE to combine cells

When carrying out data analysis with VLOOKUP it’s generally critical that the data being used has unique values.

Use & or CONCATENATE to combine cell contents and use these multiple cells (including additional text if needed), to create new cell value.

to fill out a cell using contents of one and appending from another

e.g. to create new value A1B12

A B C < C using ‘&’ > < C using CONCATENATE >
1 Jason Dorsett JasonDorsett =A1&B1 =CONCATENATE(A1,B1)

Specified text can be added between “”

e.g. to create new value A1_B12

A B C < C using ‘&’ > < C using CONCATENATE >
1 Jason Dorsett Jason_Dorsett =A1&”_”&B1 =CONCATENATE(A1,”_”,B1)

You can extend the function as needed using multiple text entries and cells

e.g. to create new value A1_B12

Car = Ford Escort – Red use

="Car = "&A1&" "&B1&" - "&C1
A B C D < D using ‘&’ > < D using CONCATENATE >
1 Ford Escort Red Car = Ford Escort – Red =”Car = “&A1&” “&B1&” – “&C1 <=CONCATENATE("Car = ",A1," ",B1," - ",C1)
2 Lotus Elise Silver Car = Lotus Elise – Silver =”Car = “&A2&” “&B2&” – “&C2 =CONCATENATE(“Car = “,A2,” “,B2,” – “,C2)
3 BMW 330 Blue Car = BMW 330 – Blue =”Car = “&A3&” “&B3&” – “&C3 =CONCATENATE(“Car = “,A3,” “,B3,” – “,C3)
4 Audi Car = Audi – =”Car = “&A4&” “&B4&” – “&C4 =CONCATENATE(“Car = “,A4,” “,B4,” – “,C4)
5 Mini Cooper Blue Car = Mini Cooper – Blue =”Car = “&A5&” “&B5&” – “&C5 =CONCATENATE(“Car = “,A5,” “,B5,” – “,C5)

 

« Older posts

© 2019 tecivo BLOG

Theme by Anders NorenUp ↑