Tech2 Touch

Category: Advanced Excel Formula

  • EXCEL XLOOKUP Function – Advantages and examples

    XLOOKUP is the latest addition to Excel lookup family of functions. It is much more functional excel formula than VLOOKUP or HLOOKUP. In this post, let us explore all about this new EXCEL XLOOKUP function with Syntax and examples.

    XLOOKUP formula is available only on Office 365 at the time of writing this. It may roll out to other version in the coming months.

    What is XLOOKUP? How it is different from VLOOKUP or HLOOKUP?

    XLOOKUP allows us to search for a value in a range or table and return the matching result. It is similar to VLOOKUP/HLOOKUP but, it offers so much more. For example, VLOOKUP can do only vertical lookup and HLOOKUP will do only horizontal lookup. XLOOKUP can be used for vertical or horizontal lookup. We will explore more on this in our example section.

    XLOOKUP has just 3 mandatory parameters and 3 optional parameters.

    The mandatory parameters are

    1. The value you are looking for.
    2. The list where the value to be searched.
    3. The list where you want the result.

    Advantages of XLOOKUP in EXCEL

    • XLOOKUP is simple and easy to use: You just need to write =XLOOKUP(What you want to find, list where you want to find, result list). You will get the answer. It is less error prone as well. No need to use the index number or define on whether you want to exact or relative match.
    • Supports right to left lookup: By default, this function has support for right to left lookup.
    • IFERROR is built in as parameter: Most of the times, we will wrap VLOOKUP formula with IFERROR to hide the #N/A. However, XLOOKUP has 4th parameter in which you can define the default output if the the value is not found.
    • Reference as output not the value: This means that, you combine XLOOKUP outputs with other formulas to get innovative outputs.

    XLOOKUP Syntax

    Basic formula with mandatory fields

    =XLOOKUP(What you want to find, list where you want to find, result list)

    Optional Parameters

    Only 3 parameters are mandatory for XLOOKUP formula. However, you can use optional 4th, 5th and 6th parameters to explore additional features.

    4th Parameter in XLOOKUP: IF not found: This parameter can be used to define what to do incase if search value is not found. Instead of returning #N/A, you can get your optional output.

    5th Parameter in XLOOKUP: Match Mode or type: By default, match type is exact match. However, you can choose between Exact match or next smaller item or next larger item or Wildcard character match.

    6th Parameter in XLOOKUP-Match direction: By default direction is top down. If you want bottom up, you define it in the 6th parameter.

    XLOOKUP in EXCEL examples

    Let us take the sample data sheet below.

    Example 1: As a alternative to VLOOKUP:

    =XLOOKUP(A2,F2:F10,G2:G10)

    This will look for the value in A2 in the range F2 to F10 and return the corresponding value in the range G2:G10. The result will be “Sales”.

    Example 2: Right to Left Lookup

    One of the advantage with XLOOKUP is right to left lookup. So to get the name, use formula as given below.

    =XLOOKUP(A2,F2:F10,E2:E10)

    This will look for the value in A5 in the range F2 to F10 and return the corresponding value from the range E2 to E10. That is “Jill”.

    Example 3: Lookup bottom up

    When there is a duplicate values in lookup range, you can choose first from the top or first one from the bottom by using the 6 parameter. -1 to bottom first and 1 for top first.

    =XLOOKUP(A8,G2:G10,H2:H10,,,-1)

    This will look for the value in A8 in the range G2 to G10 and return the corresponding value from the range H2 to H10. As we have selected -1 as 6th parameter, it will pick first one from last. The result will be 17-Apr-2020.

    Example 4: Handling Not found error

    In XLOOKUP, you can define what to do incase the search value is not found in the lookup list by entering the 4th parameter.

    =XLOOKUP(A11,F2:F10,E2:E10,”Not Found”)

    This will look for the value in A11 in the range F2 to F10 and return the corresponding value from the range E2 to E10. As the ID “SA104” not available in the range F2 to it will return “Not Found”. You can define any value or a different formula. For example, you can define another XLOOKUP to look for the data in different table as well.

  • Find matching value in a row and return column name in Excel

    When we work in excel sheets, we are required to find a matching value in a row and return the Column name. For example, we need to calculate the lowest priced store from the price table of multiple stores for multiple products. Another example can be found at the primary reason for exits from the exit feedback form analysis. In these cases, even multi-condition vlookup doesn’t help. We will take the first example and find out how to solve this using the Excel formulas.

    Recommended Reading:

    Conditional VLOOKUP with MATCH In EXCEL
    Example:

    Excel Lookup

    In the above table we have to return the company name which has lowest price for that product. For this we need to find the column which has the lowest price. So we need to use min(Range) excel worksheet formula. This will give us which is the lowest value. Now we need to find the relative position of the value. For this, match worksheet formula is useful. Now have to get the column name of that position. For this we need to use index function. The final formula will become like below.

    =INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0)) which returns Company A.

    How it works?

    MIN(B2:E2) returns 16999.
    MATCH(MIN(B2:E2),B2:E2,0) returns 1
    INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0)) returns Company A as column 1 in range B1 to E1 is B1.

    Note: The column range for index and match should be the same. Only the row numbers can be different. As match function returns relative position data, the column range should be same for Index function also.

    You can tweak this function according to your requirements. You might required to find the column name when you know the exact value in the row. In that case, you need to replace min function with required value.

  • Creating conditional drop down list in Excel

    In my earlier post, I have shown you how to create a simple drop down list in Excel. That meets most of our day to day requirements. However, when we create a dashboard or a template, we need to create a dependent drop down list or a conditional drop down list in Excel. That second drop down content should be changed based first dropdown content. It is very is to implement. So let us explore the way to create conditional drop down list in EXCEL.

    Steps to Create conditional drop down list in Excel

    To create a conditional or a dependent drop down list you need to use basic HLOOKUP function if your lists are organised vertically or vlookup function if your list is organised horizontally.

    Let me you show you how to create a dependent or conditional drop down list with a simple example.

    We have three different mobile manufacturers name in the top and list of model names down the list in sheet1 as shown in the sample table below.

    First you define the lists with the name of the manufacturer. In this example, I have created the name ‘Samsung’ for the range A2: A10, ‘Nokia’ to the range B2:B10, ‘Sony’ to  C2:C10. Like this you can create any number of lists.  You create these named ranges by going to ‘Formulas->Define Name’.

    Now we need to create the first drop down list in sheet2 where we want to create the dependent conditional drop down.

    • The first drop down list is the simple one. Just go to Data->Data Validation->Data Validation.Under validation criteria, select list under ‘Allow’ and select the source as the headings of your lists. That is Sheet1, range A1:C1. Your first drop down list is ready.
    • Now the second drop down list is dependent list. To achieve this we use, INDIRECT function and HLOOKUP function.
    • Go to  Data->Data Validation->Data Validation. Select list and the source enter the formula as mentioned below and click Ok.
    • =INDIRECT(HLOOKUP($A$2,Sheet1!$A$1:$C$1,1,FALSE))
    • Your dependent drop down list shows now. As you change the first drop down, second drop down list changes.

    How this works

    The INDIRECT functions, tells the excel that, the source is a reference and not a value or text. HLOOKUP function returns the name of the group based on the first drop down. Similarly you can do it with VLOOKUP if your lists are organised in vertical lists.

  • Excel Tip To Calculate The Number of Work Days Between Two Dates

    There are many situation where we need to calculate the difference between two dates. We can get the no of days by subtracting the one date by one date. However, that gives the total days between these dates in which includes weekly offs and Holidays. To overcome this, excel provides a dedicated function called NETWORKDAYS.
    The Excel NETWORKDAYS function calculates the difference between two dates excluding weekend days(Saturday and Sunday).  You can even provide the list of holidays in between the two dates two excludes that many days. The holidays are different for different regions. So excel by default will not be able find which dates are Holidays. Because of this we need to give list of Holidays to get the exact working days excluding holidays and weekly offs.
    The Syntax of NETWORKDAYS Excel Function
    =NETWORKDAYS(Start_Date, End Date,[holidays])
    Where
    Start Date is the starting date and End Date is the closing Date or Finishing date.
    Holidays is the the range which contains the dates which falls under Holiday

    Example for NETWORKDAYS Function
    Let us take example in which we have 01-July-2013  in  Cell A1 and 31-July-2013 in cell B1. And the list of Holidays are kept in the range D1:D3. The formula to calculate the exact working days between these two days as mentioned below.
    =NETWORKDAYS(A1,B1,D1:D2) which gives result as 21. We have total 31 days between these days. In that we have 8 weekly offs and two Holidays. So the formula gives 31-8-2=21.

  • Format Cell using EXCEL function

    In EXCEL, we can format the numbers into different formats using the Format option. In some instances, we need to format the cell value which is a result of another formula. In other output from EXCEL format should be in the desired format. There is a EXCEL function called TEXT. This is useful for this. In this post we will learn the way to format cell using a EXCEL function TEXT.

    Format Cell Values using EXCEL Function

    We can use TEXT excel function to Format Cell in Excel. This method is useful when we need to implement formatting inside a EXCEl formula.

    Syntax of Excel TEXT Function

    =TEXT(value, format_text)

    Where
    Value is the number which we want to change the format.
    format_text is the required format.

    Examples of converting the format of values in Excel using formula

    We have a value 41479 in cell A1.
    =TEXT(A1,”dd-mmm-yyyy”)
    The above formula changes the format of the value in cell A1 to dd-mmm-yyyy format(24-Jul-2013).

    Similarly you can use any format in which you want to display the number. Below are some more example for the same.

    • =TEXT(A1,”#,##0″) this will give output as 41,479.
    • =TEXT(A1,”£#,##0;-£#,##0″) which gives output in the format £41,479.

    Like this you can use your own formatting using this excel formula. The biggest advantage of this excel function is that you can use this formula inside another formula or function to format the output.

    For example you want to concatenate date and a text . In Cell A1 we have “Today Date is” and in cell B1 we have 24-Jul-2013. Now if we use normal concatenation.
    =A1&B1 the output will look like this. Today Date is41479. So we need to use the text formula to convert the number in cell B1 to Date and then we can concatenate. The modified formula look like this.
    =A8&TEXT(B8,”dd:mmm:yyyy”) which shows the output as Today Date is24:Jul:2013.

    You can even use this formula with other excel formulas such as vlookup, hlookup. The formula to change the format of vlookup formula out is as mentioned below.
    =TEXT(VLOOKUP(B15,A13:B13,2,FALSE),”DD:MMM:YYYY”)

    Hope this helps to create your excel templates by using the Formula to Format Cell.