CODEWITHSUNDEEP
excel
Pr0no
Pr0no

Reputation: 4099

Excel: How to leave cell empty (instead of 0) when VLOOKUP has no match?

I have an excel workbook with 2 worksheet: countries and companies. The companies worksheet is partially filled-in through VLOOKUPs in the countries worksheet, as follows:

countries:

     A       B
1 COUNTRY HOF_LTO
2 Belgium    4
3 Japan      5
4 Spain
5 Sweden     0

companies:

    A         B       C
1 COMPANY  COUNTRY HOF_LTO
2 Belgacom Belgium    4
3 Onkyo    Japan      5
4 Sony     Japan      5
5 ATTM     Spain      0
6 Nokia    Sweden     0

I'm filling the C column by using the formula:

=VLOOKUP($B2;countries!$A$1:$C$5;MATCH(companies!B$1;countries!$1:$1;0);FALSE)

What goes wrong is: even though Spain does not have a HOF_LTO-value in countries, the formula puts 0 as the appropriate value in cell companies!C5. Instead it should leave the cell empty and only put in a zero (0) when it says 0 in the countries tab, as it does in the case of Sweden.

How do I need to edit the formula so it will leave cells empty when there is no country value to match?

Upvotes: 18

Views: 78579

Answers (8)

Andreas Covidiot
Andreas Covidiot

Reputation: 4765

Another solution is to use(*) a UDF (user defined function) vlookup2 that simplifies the typical vlookup usage a lot (and honors good performance and more readable formulas).

So the usage would be in the given example:

=VLOOKUP2(
   $B2  ;  countries!$A$1:$C$5  ;  MATCH( companies!B$1 ; countries!$1:$1 ; 0 )  ;  "" )

instead of e.g. (based on the nice Pillowcase example):

=VLOOKUP( 
   $B2  ;  countries!$A$1:$C$5  ;  MATCH( companies!B$1 ; countries!$1:$1 ; 0 ) ; FALSE) 
   & ""

in other cases (where e.g. the search criteria $B2 may already be #NV or similar the following could make sense:

=VLOOKUP2(
   $B2  ;  countries!$A$1:$C$5  ;  MATCH( companies!B$1 ; countries!$1:$1 ; 0 ) ; "" ; "")

The code for vlookup2 would be:

'simpler to use than vlookup
'only exact matches and 0 subst value support
'https://stackoverflow.com/a/55743343/1915920
'
Function vlookup2( _
  ByRef searchCriteria As Variant, _
  ByRef matrix As Variant, _
  ByRef columnIndex As Variant, _
  Optional zeroSubst As Variant, _
  Optional errSubst As Variant )

    Dim val As Variant:  val = Application.WorksheetFunction.VLookup( _
                                 searchCriteria,  matrix,  columnIndex,  False )

    If Not IsMissing(errSubst) Then On Error GoTo EH  'EH = error handler
    If Not IsMissing(zeroSubst) And val = 0 Then val = zeroSubst

    vlookup2 = val
    Exit Function

EH:
    vlookup2 = errSubst        
End Function

(*): if you don't know something about it, it is as simple as this:

  1. push CTRL + F11 => Microsoft Visual Basic for Applications window opens
  2. insert a new module and paste the above code in the right editor enter image description here
  3. close just opened window

For the german version one could add another one: 

'https://stackoverflow.com/a/55743343/1915920
Function SVerweis2( _
    ByRef Suchkriterium As Variant, _
    ByRef Matrix As Variant, _
    ByRef SpaltenIndex As Variant, _
    Optional NullZahlSubst As Variant, _
    Optional FehlerSubst As Variant)

    SVerweis2 = vlookup2( Suchkriterium, Matrix, SpaltenIndex , NullZahlSubst, FehlerSubst )
End Function`

Upvotes: 0

Chiramisu
Chiramisu

Reputation: 4726

The cleanest way I've found for text (pun intended), is to use the CLEAN function. :)

Note: This should only be used for columns where you are returning actual text. You can just omit this wrapper if you are returning numbers, dates, etc.

Simply wrap your VLOOKUP with the CLEAN function like so:

=CLEAN(VLOOKUP($B2,countries!$A$1:$C$5,MATCH(companies!B$1,countries!$1:$1,0),FALSE))

Even better, wrap it all in an IFERROR to make sure you don't get #N/A or the dreaded #REF!, etc. back; like so:

=IFERROR(CLEAN(VLOOKUP($B2,countries!$A$1:$C$5,MATCH(companies!B$1,countries!$1:$1,0),FALSE)),"")

Bonus: FIXED! All your formulas were incorrectly using semicolons instead of commas between the function arguments, and I'm rather surprised nobody caught that. >.<

Generic:

=IFERROR(CLEAN(VLOOKUP(lookup_value, table_array, col_index_num,FALSE)),"") - For Text =IFERROR(VLOOKUP(lookup_value, table_array, col_index_num,FALSE),"") - For Values =IFERROR(VALUE(VLOOKUP(lookup_value, table_array, col_index_num,FALSE)),"") - For Values Forced

Upvotes: 0

Lawrence Buckingham
Lawrence Buckingham

Reputation: 11

Here is a variation on the VLOOKUP + concatenation answer which ensures that numeric values do not get converted to strings:

=IF(LEN(VLOOKUP(args) & "")=0,"",VLOOKUP(args))

Upvotes: 1

Pillowcase
Pillowcase

Reputation: 714

This should solve it:

=VLOOKUP(...) & ""

This will force Excel into making that cell reference a text value, thus preventing the conversion of blanks into zeroes.

Taken from https://superuser.com/a/906954/222835

Upvotes: 46

craig
craig

Reputation: 81

=CONCATENATE(VLOOKUP($B2;countries!$A$1:$C$5;MATCH(companies!B$1;countries!$1:$1;0);FALSE))

Concatenate ensures the data is treated like a string instead of a number. If the value is just an empty cell, you'll simply get an empty cell.

Upvotes: 8

Bill
Bill

Reputation: 11

Simple solution: Do a find and replace on the full array of your countries source workbook, replacing empty cells with @. (make sure "Find entire cells only" is checked when you do the Replace All.) Then go back and run your vlookups. Copy and paste values to get rid of the vlookups and only keep the results. Now search and replace @ symbols with nothing in the destination file. Voilà.

Upvotes: 0

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Alternatively, if it is just the presentation of the data that you need to control, adjust the cell formatting to display 0 values as blank instead of as the digit zero.

Upvotes: 0

K_B
K_B

Reputation: 3678

If in your country sheet all countries are only present once you could use the following in stead:

=IF(ISNUMBER(OFFSET(countries!$B$1;MATCH(B2;countries!A:A;0)-1;0));
    OFFSET(countries!$B$1;MATCH(B2;countries!A:A;0)-1;0);
    "")

Upvotes: 4

Related Questions