Tech2 Touch

Category: Lookup Formulas

  • Excel Vlookup Formula with IF Function-Condition Based vlookup

    I have already written on basic vlookup function and conditional vlookup function. In this Excel tutor, i will show you how you can use vlookup function along with IF function. Recently i came across a situation where i need to use different vlookup formula when there is a different scenarios. So i thought even this one will help you also. This excel tips guide will help you on how to perform different range conditional vlookup in excel.

    Using VLOOKUP formula with Nested IF function

    Actually, i came across this formula, while i was working on a annual salary revision data for a company. There it had all the countries employees with different grades and bands in one sheet. I have been provided with a grid which has increment amount for each grade, grade upgrade and promotion for different countries.  The employee data has country details, previous grade, current grade and the band.  One way is to split the employee data into three different sheets and performing. But i had a restriction on that. I should do for all countries in one sheet. Below is the steps I have done.
    Nested IF and Vlookup Formula Example
    1. I have created one more table for the Proposed Salary increment Grid. A unique id created by concatenating the previous grade, current grade and Band with the help of separating operator !. Please refer the below excel sheet. From A1: F12 contains original data table. From H2: K 12, the calculated salary increments for the different scenarios.
    2. Then created the unique code in the employee sheet too with the same logic of concatenating  previous grade, current grade and Band with the help of separating operator !. In the example, employee data is in A15 to G21. Added unique code in Column G.
    3. Now, in the increment amount field column F17, inserted the vlookup formula with Nested If function as mentioned below.

    =IF(B17=”India”,VLOOKUP(G17,$H$4:$I$12,2,FALSE),IF(B17=”US”,VLOOKUP(G17,$H$4:$J$12,3,FALSE),VLOOKUP(G17,$H$4:$K$12,4,FALSE)))

    4. Copied the same  formula to all the employees. The employee sheet has the increment amount for all the employees based on their grade upgradation or promotion and their countries.

    The above logic is simple, Tested the condition and while the condition is met, executed one set of vlookup formula while that condition is not met, tested one more criteria and ran different set of vlookup formula and so on.  By default you can Nested if for 7 times. For using IF formula more than 7 times, you need to take help of Excel VBA custom function.

    Share your thoughts on the same. If you have any better solution for this or if you need any clarification on this, share it in the comment section below.

  • 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