Friday, 17 February 2017

IFERROR with VLOOKUP And LEN AND LENB FUNCTION

Combining the IFERROR Function Formula with Logical VLOOKUP Functions Formula


IFERROR Function With VLOOKUP Function Formula


Description : - 

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR Function With VLOOKUP function to trap and handle errors in a formula


Below Following Values if Error : -

#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!

For Example
A                            B                        C                           D
Description          Value            Description        Value Lookup
Product                #N/A             Product                0
Product Details   #VALUE      Sale5                   0
Item                     #REF!           Sale                     0
Sale                    #N/A             Item                       0
Sales                  #DIV/0!        Name                    0
Name                 #NAME?     Num                      0
Num                   #NUM!         Null                        0
Null                     #NULL!       Product  No.         125
Product No.      125              Product Data        220
Product Data    220              Product Details    0



                       
IFERROR With VLOOKUP Formula

=IFERROR(VLOOKUP(D:D,A:B,2,FALSE),"0")


Here the Below Screenshot


 

 

​How to Use LEN and LENB Function Formula Using in Excel

LEN and LENB functions Formula in Excel

Describes the formula syntax and usage of the LEN and LENB functions Formula in Excel

Description : -

* LEN Count the number of characters in a text string


* LENB Count the number of bytes used to represent the characters in a text string.



For Example : 

* LEN Function Count the number of characters in a text string = Total Character is 51

   LEN Formula in Excel  =LEN(A1)


LENB Function Count the number of bytes used to represent the characters in a text string = Total Bytes is 80

   LENB Formula is =LENB(A1)



Important : LENB Function counts 2 bytes per character only when a DBCS language is set as the default language. Otherwise LENB behaves the same as LEN, counting 1 byte per character

 

 

 

No comments:

Post a Comment