Tech2 Touch

Tag: VLOOKUP in Excel

  • VLOOKUP Tips and Tricks- EXCEL Tutorial

    VLOOKUP in Excel is one of the most powerful and useful function which helps to save our time on many occasions. Some of the situations where VLOOKUP comes in handy are fetching data from other tables, matching two different series, creating one list from multiple related lists etc.  This post is not on how to use VLOOKUP in excel. This post is all about some of the useful tricks which is helpful to use VLOOKUP in excel in an effective way.

    VLOOKUP Tips and Tricks

    1. Using the $ for lookup value and lookup range

    When you are copy and pasting the VLOOKUP in excel to different cells, you might have noticed errors. This happens because while you are pasting the formula, range changes. To avoid this, you can use the absolute range or value. To make the value absolute you should use $ symbol.

    For example, consider an example.

    =vlookup(a2,c2:d5,2,false)

    The above formula is there in cell G2. If you copy and paste the formula in G3, the formula changes to  =vlookup(A3,C3:D6,2,false). If you copy the formula in the cell H2, then formula changes to =vlookup(B3,D2:E6,2,false). To avoid this, we can use $ or it is called absolute reference.  $ makes the row or column absolute. If you use before column name then, the column will become absolute. If you use before row number, then row number will become absolute.  Likewise, if you use $ before column and row number, then both column and row will become absolute. There are different ways to use absolute references in excel depending on the situations. Let’s learn more on this with the same example mentioned above.

    Scenario 1: =VLOOKUP($A$2,$C$3:$D$6,2,false)- In this case, wherever you copy this formula, it will lookup for the value in cell A2 in range C3 to D5 of the active sheet.

    Scenario 2: =VLOOKUP($A2,$C$3:$D$6,2,false)- Here lookup range and column of the lookup value remains constant. However, row number changes depending on where you paste the formula. This can be used when lookup value in there in column A.

    Similarly, you can use the absolute reference for the range as well.

     2. Understanding error values of VLOOKUP

    Majorly, while using VLOOKUP in excel 3 errors values come out. They are #NAME, #N/A and #VALUE.

    What is this means? How to fix it.

    #NAME error in VLOOKUP:  If you get #NAME error, then it means that there is a spelling error in the formula. Please check the syntax and fix it.

    #VALUE error in VLOOKUP: This commonly appears when the column index number is less than the range you have selected. For example, if you have selected the column index number as 5 wherein the range has only 4 columns this error will come. Please check the lookup range and column index to fix this. Another possible cause might be lookup value length is more than 255 characters.   

    #N/A error in VLOOKUP: This is not necessarily an error.  This means that the value you are looking for in the range is not available.  The things you should check are

    • Is lookup value is correct?
    • Are any blank spaces before or after the lookup value? If yes use trim or clean functions to remove spaces.
    • Have you had selected the range where the first column has the value you are looking for lookup value? -If no, select the range such way that, a column on which you are looking for the lookup value should be the first column in the range. The format of the lookup value and lookup range should be same.

    3.  Working with errors

    When the lookup value is not available in lookup range, VLOOKUP gives error #N/A. If you are using the further calculation based on the resulting result, then that also shows as #N/A. To hide this or to make cells look cleaner you can use the formula IFERROR with VLOOKUP. It will look like this IFERROR(VLOOKUP(….), “VALUE to be WRITTEN IN CASE OF ERROR”)).

    4. ROW and COLUMN VLOOKUP or multi conditional VLOOKUP

    We already know that VLOOKUP in EXCEL can be used for fetching data based on the lookup value. But how to use multi conditional VLOOKUP in EXCEL. This means that the resulting data should be matched with both row and column. This can be done with help of MATCH function with VLOOKUP. You need to replace, column index or 3rd argument of VLOOKUP with match function.

    Example for the same is as mentioned below.

    =VLOOKUP($A9,$A$1:$D$6,MATCH(B$8,$B$1:$D$1,1)+1,FALSE)

    You can read more on this here.

    5. Partial match with VLOOKUP

    Making forth argument of VLOOKUP as TRUE will perform partial match. But it has many restrictions. Instead, you can use wildcard characters in VLOOKUP function to perform partial match. Basic wildcard charecter for partial match are,

    “*”: Find any number of characters after or before text. For example, you can use “Te*” to match the text “Text” from a list or *Te to match the text “Forte”.

    “?”: Use a question mark to replace with a character. For example, you can use “?aste” to lookup for the text “Waste” or “Taste”.

    These are the basic yet effective VLOOKUP tips to make your work easier and use this function more effeciently. If you have any queries do write in the comment section below.

  • VLOOKUP in Excel Tips and Tricks

    We already learned on how to use VLOOKUP in Excel.  In my earlier different posts, i have shown, the different ways you can use vlookup functions. This post is the summarisation all the things which we have already discussed.

    VLOOKUP in Excel Tips and Tricks

    Here is the list of few VLOOKUP in Excel tips to use the excel worksheet function more effectively.

    1. Use named Ranges:  the vlookup function with normal range variables looks very complicated. To avoid this, use named ranges.  With named ranges, you can do dynamic ranges which saves lot of memory and by that, the time. You can read more dynamic ranges in excel here.
    2. 2 way or conditional vlookup: The default vlookup accepts only single criteria lookup. But using the match function, you use vlookup function which looks for the given value in row as well as in columns. The syntax is same as the normal vlookup apart from inserting match function instead of index number. The match function returns the reference number of the column. To learn more on this read my post on conditional vlookup.
    3. Handling error values in Excel: When the lookup value is not found, vlookup returns, #na. You can remove this by using iferror function. This will make your excel template more clean and easy to perform calculations
    =IFERROR(VLOOKUP(B16,A2:C6,2,FALSE),0)
    4. Use Wildcard characters for related lookup: You can use related lookup using the last argument as TRUE. but, it has a downside. The range should be in ascending order. The wildcard character will help in these situations. Below are the few examples.
    =VLOOKUP(“Galaxy*”,A2:C8,3,FALSE)- returns the value from the range which starts with Galaxy. If there are multiple Galaxy are there, then it will take the first one.
    =VLOOKUP(“????”,A2:C8,3,FALSE) this look for a value with the lookup value length is 4.
    Similarly you can use most of the wildcard characters in vlookup also.

    If you find these tips on VLOOKUP in Excel, or have any queries, feel free to leave feedback in the comment section below.

  • Excel If Function – Syntax And Examples

    Excel IF function is one of my favorite function in Excel. It is very useful and very simple to use. It does wonders when you want to create some complicated excel Formulas. You can extend the functionalities of IF function by nesting it or using along with other Excel Functions such as VLOOKUP, AND, OR etc.
    How Excel IF Function Works?
    Excel IF Function  checks for a condition(Criteria) and returns a value if the condition is met or returns another value if the criteria is not met. The criteria can be anything such as comparison, text check or error check etc. You can use nested IF functions upto 98 times in a excel worksheet.

    Syntax of Excel IF Function

    =if(condition, value if the condition is met, value if condition is not met)

    Examples:
    =if(A1>10, “Greater Than 10”, “Less than 10”)

    The Above IF formula returns Greater than 10 if the cell A1 has the value greater than 10 else returns Less than 10.

    Using IF function with AND

    As said earlier, you can use AND function inside IF  while you want a value to be written provides multi conditions were met. AND function returns TRUE if and only if  all the conditions are TRUE.For example, you have a value in cell A1. You want to check whether the number is in between 10 to 15. You can use the IF function as mentioned below.
    IF(AND(A1>10,A1

    Using IF function with OR

    IF and OR is used when you want to check any of the conditions is met. The OR Function returns true if any of the condition is met. For example you have want to check out of range if the number in cell A1 is less than  0 or greater than 100. You can use IF function with OR to calculate this as mentioned below.
    IF(OR(A1>100,A1

    Using Nested IF Function

    Using IF functions inside another IF function is called as nested IF functions. It is useful when you want to dig into the more detailed checks. For example you want to check if the number you have entered is a negative or positive in the cell A1.  First we need to check whether the entered value is number. If it is a number, then we need to check whether it 0. Then we need to check whether it is greater than 0 or less than 0 to find out the value entered is positive or negative. The nested IF function in Excel looks like as mentioned below.
    =IF(ISNUMBER(A1), IF(A1=0,”NIL”,IF(A1>0,”Positive Number”,”Negative Number”)),”It is not a Number”)
    The above uses ISNUMBER function to check for the whether the entered value is number or not.
    To learn how to use IF function with vlookup read my post on how to do vlookup on different ranges based on criteria.

  • How to Perform two column Lookup on Excel

    The VLOOKUP function in excel does not handle multiple criteria by default. However, with little tweaking the source data or with the help array formula we can perform multi condition lookup. In this post, we will learn the way to do two column lookup on Excel.

    Let us take a sample data.

    Perform two column Lookup on Excel

    To make this formula we have defined user named ranges.

    Range(C4:D6) is named as lookup_Table

    Other related posts:

    VLOOKUP in EXCEL Tips and Tricks

    ROW and Column VLOOKUP

    Perform two column multi condition vlookup using the helper column

    As VLOOKUP does not take multiple criteria, we need to insert a new column called helper Column. In this column we need to concatenate the fields of two column. In our example, we have concatenated Brand and Model using the formula concatenate or with the help of “& “.

    Formula in cell C4 is

    =A4&B4

    Final vlookup formula is

    =VLOOKUP(G3&G4,lookup_Table,2,FALSE)

    How this conditional vlookup works

    In the example, we want to lookup price using vlookup based on brand and model.

    One of the limitation of VLOOKUP is it handles only one condition. The lookup value will be searched in first column of the selected data range. To overcome this limitation, we are using the helper column.

    In the helper column, we have concatenated the Brand and model. The lookup value also we have joined together. In the example, VLOOKUP searches for BajajPulsor in the range C4 to C16. It is there in 9 the row. Now it will return the corresponding value in 2nd column. That is 53000.

  • Using VLOOKUP on Excel-Explained with Syntax and Examples

    VLOOKUP functions of excel is very useful when you need to return a value from a table or a range of cells by looking up another value. VLOOKUP on excel can be used for finding the value in a range which are present in the other range in the same sheet or other sheet and other excel files. Let is quickly look into the syntax of vlookup function and examples on how to use VLOOKUP on Excel.  Do note than simple VLOOKUP function works with only single criteria. If you are looking for 2 or more criteria vlookup, the please refer my previous post multi criteria vlookup.

    Syntax of Excel VLOOKUP Function:

    = VLOOKUP( value, table_array, index_number, Typeoflookup )
    where
    Value: is the value to search for in the first column of the table_array or range of cells. Value can either a text or a numerical value.
    table_array: is the range of cells where you need to lookup for the value.
    index_number: is the column number in table_array from which the matching value must be returned. A index_number argument of 1 returns the value in the first column in table_array; a index_number of 2 returns the value in the second column in table_array, and so on.
    Typeoflookup: This is not mandatory. However this will chose between exact match and relative match.  If you use TRUE or if you omit this argument, then non exact match lookup will be performed. If this is set to FALSE, then vlookup does the exact match
    Note and Troubleshooting Tips on Excel:
    • If  “Typeoflookup” is either TRUE or omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
    • If “Typeoflookup” is FALSE, the values in the first column of table_array do not need to be sorted.
    • Excel vlookup returns #VALUE! if  “index_number” is less than 1 and returns  #REF! if the  “index_number”Greater than the number of columns in table_array. Also one more error value comes with VLOOKUP is #N/A. Excel returns #N/A when there is no exact match or the value you are matching in the range is not found.
    •  If there are two or more values in the first column of table_array that match the lookup_value, the first value found will be returned

    Example on Using VLOOKUP formula in Excel:

    Excel Examples
    Sample data

    Using vlookup with Exact match:

    Considering above sample data, if you need to lookup for the value “Banana” and fetch the Rating, use the formula given below.
    =VLOOKUP(“Banana”,A2:C6,3,FALSE) which returns C.

    Using vlookup with non Exact match:

    This is useful, when you need relative value. Say for example, your matching value based on the “% of commission” and you want to return the ratings. Here do not that, for using non exact match the first column of lookup range  must be placed in ascending sort order.
    =VLOOKUP(33%,B2:C6,2,TRUE) which returns C. Here first instance of nearby value is taken by the formula.

    Formatting the VLOOKUP on Excel using ISNA and IF functions:

    When there is no match found, excel returns #n/a.  This is will give problems when you need to use SUM function on the return value of returned value.To overcome this, we need to use ISERROR function. This very useful, if you are creating a template with VLOOKUP function.

    Examples:
    From the same above sample table, we will match Strawberry. The formula returns #n/a as the match is not found on the table.
    =IFERROR(VLOOKUP(“Strawberry”,A2:C6,3,FALSE),”Not Found”) which   returns “Not found”.
    The IFERROR functions syntax is =IFERROR(Value, value if error) where “value” is the data which needs to checked for error and “value for error” is the value to be returned incase of error.
    In above example we use VLOOKUP return data as value and “Not Found” used if the returned value is #N/A.
    IFERROR function was introduced in Excel 2007. Hence if you are using earlier version of Excel 2007, then you need to use IF function and ISERROR function of Excel. So the formula is as shown below.
    =IF(ISERROR(VLOOKUP(“Strawberry”,A1:C6,3,FALSE)),”Not Found”,VLOOKUP(“Strawberry”,A1:C6,3,FALSE))
    Note: Do not use this function unless you have earlier versions of excel as this will lead to calculation time. This is because for every time you use the function, excel needs to calculate vlookup twice. when range to lookup is more and number of instances of using this formula more, then excel will take really long time to calculate and return the right values.
    Please do share your feedback and tips which you know in the comment section below. If you need any specific support on Excel, then mention your query in comment section, I will try to answer your query.

    Video Tutorial for VLOOKUP