Tech2 Touch

Author: Aniruddh

  • Multi Conditional lookup in Excel

    Have you ever came across a situation wherein you wanted to return a value which matches both row and column? VLOOKUP function in Excel returns the value which is matching the row only. In our previous tutorial, we have shown how to perform two column lookup. In this excel tips post, we will guide you on how to perform multi conditional lookup using match and index functions.

    Limitations of Vlookup:

    • VLOOKUP can be used only if the lookup value is in left of the data which we need to extract from the Table or data.
    • VLOOKUP works with one criteria. That is, lookup value is maximum one.

    Multi Conditional lookup in Excel

    To overcome above-mentioned limitations of VLOOKUP, we can use match and index function of excel to get a result like conditional VLOOKUP.

    Using Match and Index function for conditional lookup

    When Match and index functions of excel used together, we can extract the data from a table irrespective of the weather lookup value is left side or right side of the array. So first let us understand Match and Index functions. To perform conditional lookup, we should understand how match and index functions of excel work.

    Excel Index Function

    Excel Index function returns a value or reference from a table or range.

    Syntax of Excel Index Functions:

    =INDEX(array, row_num, [column_num])

    Explanation of Index Function components

    • Array: Is a range or table where we need to extract the data.
    • row_num: In which row the required value is there.
    • [column_num]: In which column the lookup value is present.

    =INDEX(A1:C5,2,3)  returns 3. We are looking for a data which is 2nd row and 3rd column.

    Excel Match Function

    Match function returns relative position of the specified item is a range of cells.

    Match Function Syntax

    MATCH(lookup_value, lookup_array, [match_type])

    • Lookup_value: The value you need to look up.
    • Lookup_Array: The range where you need to search
    • [match_type]: Match_type can be -1, 0, or 1. It tells Excel how to match the lookup_value to values in the lookup_array.
      • 1 — find the largest value less than or equal to lookup_value (the list must be in ascending order)
      • 0 — find the first value exactly equal to lookup_value. Lookup_array (the list can be in any order)
      • -1 — find the smallest value greater than or equal to lookup_value. (the list must be in descending order)

    Note: If match type is omitted, by default excel consider it as 1.

    Example for Excel Match Function

    Consider the same table as above.

    =MATCH(“Sahadeva”,A1:A5,0) returns 5. That is the value “Sahadeva” is in 5th row.

    Using Index and Match together as an alternative to vlookup

    Using index match together will help us in finding 2 criteria lookup and values are present in left of the lookup value.

    Generic Formula

    =INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), MATCH(lookup_value, lookup_array, [match_type]))

    Here what we did is instead of finding row and column numbers we used Match function to find.

    Example:
    =INDEX(A1:C5,MATCH(“Sahadeva”,A1:A5,0),MATCH(“Class”,A1:C1,0)) This returns 6.

    How to MATCH and INDEX work as alternative to VLOOKUP

    We are looking into the table as the range: So, in Index, we used Table as the range. Using Match function, we found rows number of our lookup value “Sahadeva”. For the column number, we once again used Match function to find another criteria column number. That is we are searching for Sahadeva’s Class. So, Class is in 3 row. So, the function returns the value which is in the 5th row and 3rd Column.

  • How to Change Default search in Mozilla Firefox

    Mozilla Firefox is one of the most popular browser today. It is very fast and safe for browsing. Earlier the default search in Firefox browser was Google. But recently it has changed to Bing by default. Some time even addons and some free software change the default search provider.  To change the default search engine in your browser below are the steps given. Mozilla firefox supports address bar search also. So let us first look into change the default search engine for Mozilla Firefox address bar.

    Steps for Changing default search engine for Mozilla Firefox address bar:
    In Firefox we can search simply by typing the search keywords in the address bar to get result. So below is the step to update the search engine.

    • Open about:config in Firefox (if you doing it for the first time, you may have to promise you are going to be careful)
    • Search for keyword.url from the filter box.
    • Double click on this entry. It will open a dialog where you can edit the ‘keyword.url’ string.
    • Change this string to whatever you want it to be:

  • Changing Default File Saving Location in Word

    The MS Word by default saves file in My document folder(In Windows 7 just ‘Document’).  Whenever we save a file without choosing the destination folder, Word saves the file in Document. These Document folder has restrictions. Only from the user name it created under that user name ‘Document’ folder has the right to open it. So it is ideal to save a files in a common folder or drive by default  if multiple users are using the file. Also if you are saving the documents in Documents folder, incase of OS reinstall you will lose the data. So it is highly recommended to save your documents in different drive. Unless the documents are confidential. Let us have a look into the same.

    Steps to Change default File Saving path in MS Word

    • Go to  ‘Tools’ Menu and select ‘Options’
    • In ‘Options’ dialog box,  click ‘File Location’ Tab.
    • Select the ‘Documents’ option under the ‘File Type’ Section.
    • Select ‘Modify’ button
    • Choose the folder where you need to save your files always.
    • Once done save the changes and restart Word to take place the changes.

    Steps to Change default File Saving path in MS Word in Word 2010:

    • Select ‘File’ menu from left top corner.
    • Choose ‘Options’
    • In newly opened dialog box select ‘Save’ Tab.
    • In ‘Default file location’ choose the folder where you need to save the files.
    • Once done Click on ‘Ok’ and restart Word.

  • Set Default OS while booting in Multi Boot OS systems

    Some Laptops and Desktops have multiple versions of Windows.  The default operating system is automatically sets to the latest OS we installed.  There are times Primary OS we are using is not set as default one. So below is the steps to set the default Operating systems. Microsoft is providing a simple and very easy way of setting the default version of Windows to boot. So here is the guide, on how to set default Windows OS for booting.
    Change the default OS
    Steps for Setting default OS while booting:

    • Double click on the ‘My Computer” icon
    • New screen comes up. In the new screen select ” System Properties” button
    • Then select “Advanced system settings” link from the left panel.
    • Click on “Advance” Tab
    • Click on “Setting” button under the “Startup and recovery” Section
    • Select the default Operating system which needs to be set as Default OS  from Default operating system drop down menu.
    • Click ‘OK’ to Save.

    Steps for Setting default OS while booting in Windows 7:

    • Right click on “Computer” Icon which available in start menu right side.
    • Click on ” Advance System Setting”
    • Go to “Advanced” Tab.
    • Click on “Setting” button under the “Start up and recovery” Section
    • Select the default Operating system which needs to be set as Default OS  from Default operating system drop down menu.
    • Click ‘OK’ to Save.

    Note:

    • This will work only if the multiple OS are Windows based OS like Windows XP, Windows Vista or Windows 7. This will not work if you are using Windows and Linux OS.
    • These setting needs to be changes in the Latest OS installed on your system. For example if you have Windows XP and Windows 7, then you need to set this settings in Windows 7.

  • Using Loops in Excel VBA

    Looping is used to repeatedly running a set of codes till it completes given condition. The loops are the fundamental component of any programming Language. In Excel VBA too there are many loop constructs. Let us have a close look into basic loop constructs.

    Basic Loop constructs of Excel VBA:

    • For…..next
    • Do……While
    • Do……Until
    • While……….Loop
    • For Each……Next
    For……Next Loop:

    This loop is used when there is a need to execute a statement or a block of statements for certain number of times.
    Example for For…..Next Loop:
    For i =1 to 10
         Total=Total+i
    Next i

    This code will calculate the sum of numbers from 1 to 10.  In this example statement ‘Total=Total+i’ repeats 10 times.  ‘i’ and ‘Total’ are the variable used in the code.
    In this example for For….Next loop following steps took place:
    This loop will work as long as the value of ‘i’ is more than 1 and less than 10.
    1. The For loop initializes the value of ‘i’ as 1. As 1 is less than, 10 the next statement executed.
    2. The code   ‘Total=Total+i’ is executed for first time.
    3. The code ‘Next’ increments the value of ‘i’ by 1
    4. The control shifts to the begining of the loop, where the value of the variable ‘i’ value is checked
    5. Till value of ‘i’ is less than 10 step 2 and 3  are executed.
    6. As soon as value of ‘i’ becomes more than 10  the loop terminates.