tech2touch

Author: Aniruddh

  • 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.

  • OnePlus 5T Top 4 New Features

    OnePlus 5T  finally official now after numerous leaks and rumors.  Though it is an incremental upgrade over its predecessor OnePlus 5, it offers some interesting new features which OnePlus fans were asking for. So here is the list of OnePlus 5T new Features according to us.

    OnePlus 5T

    OnePlus 5T Top 4 New Features

    1. Full Optic AMOLED display screen:  OnePlus 5T comes with 6 inch full HD Plus(1080×2160) AMOLED screen with pixel density of around 401 ppi.  Though it has a larger screen, the overall size of the device remained almost similar to OnePlus 5. Thanks to reduced bezel in the top and bottom and switching to 18:9 ratio screen instead of 16:9.  Finally, OnePlus 5T caught up with the fashion of thin bezel smartphones of 2017.
    2. Face unlock: Face unlock is not something new as a technology. But what makes OnePlus 5T unique is its implementation. It is fast and accurate most of the time. How much it is secured we have yet to be tested. At this point in time, OnePlus 5T has the fastest face unlocking system based on our experience.
    3.  Parallel Apps: This is one feature many of the users expecting from the OnePlus team.   With parallel apps feature, you can run two instances of apps. That means, for example, you can have 2 facebook account, 2 WhatsApp account etc. To enable parallel apps, go to Settings->Apps->Parallel Apps. Now enable whichever app you want to add the 2nd instance.
    4. Fingerprint scanner swipe gestures:  Fingerprint scanner was fast and accurate even on OnePlus 5. However,  lack of swipe gestures or any function to it except unlocking device was bit disappointing.  With OnePlus 5, this is changing.  There is new gesture function added to it. Swipe down to access notification, long press  on camera app to click photos to name few.

    These are the few additional features which according to us make OnePlus 5T more consumer attractive.  Though DASH charger, stock Android experience, good camera performance, flagship SoC with up to 8GB of RAM are not new, it still best in class features to look out for.

  • Moto X4 with Dual Rear Camera-FAQ Tips and Tricks Cons

    Lenovo has launched its latest Moto X4 in India. It is the first phone launched in India which is powered by new Snapdragon 630 SoC according to Lenovo. The key features of the device are IP68-rated  dust and water resistance, Dual rear cameras and Amazon Alexa support. Here is the quick Moto X4  FAQ, Tips and tricks, and Cons.

    Moto X4

    Moto X4 with Dual Rear Camera-FAQ Tips and Tricks Cons

    What are the different variants of Moto X4 launched in India?

    Moto X4 is available in two variants. One with 3GB of RAM and 32GB of internal storage and another one with 4GB of RAM and 64GB of internal storage.

    What is the price of Moto X4 in India? Where to buy one?

    Moto X4 price in India is  Rs.20,999 for the 3GB RAM and 32GB internal storage variant. 4GB RAM and 64GB internal storage variant is available at Rs.22,999. This smartphone will be sold exclusively via Flipkart and Moto Hub shops.

    What are the color options available?

    The device will be available in Super Black and Sterling Blue color variants.

    What type of SIM cards Moto X4 uses?

    Moto X4 supports hybrid sim card slot. You can use 2 nano sim cards or 1 nano sim card with microSD card.

    What are the specifications of Moto X4?

    The key specifications are as follows.

    • 2.2 GHz Octa-core Snapdragon 630 Soc.
    • 3GB/4GB of RAM with 32GB/64GB of internal storage.
    • 5.2inch full HD LTPS IPS display screen with Corning Gorilla Glass protection.
    •   4G VoLTE, Bluetooth v5.0, dual-band (2.4GHz and 5GHz) Wi-Fi 802.11 a/b/g/n/ac, GPS/ A-GPS, USB Type-C, NFC, FM radio.
    • 12MP and 8MP primary camera.
    • 16MP front facing camera.
    • 3000 mAh battery with fast charging.

    How dual camera setup in Moto X4 functions?

    Moto X4 has 12MP primary camera with a dual Autofocus Pixel sensor with a f/2.0 aperture, and 1.4-micron pixels. The second camera has  8-megapixel ultra-wide angle sensor with a 120-degree field of view, a f/2.2 aperture, and 1.12-micron pixels.

    What are the Moto X4 Cons?

    Here is the list of Moto X4 Cons based on our first impression.

    • Unimpressive design.
    • Thick bezels.
    •  Hybrid sim card slot.
    • No Android Oreo out of the box.

    How to take screenshot on Moto X4

    To take the screenshot, just press and hold volume down and power button simultaneously. The screen will be captured.

    How to set ringtone in Moto X4

    Like all previous Moto smartphones, Moto X4 also comes with stock file manager. Hence for setting the MP3 as ringtone, either one has to copy the ringtone to Ringtone folder on the smartphone or install the third party file managers.

  • Nokia 6 Cons-5 Reasons not to buy

    Nokia 6 is the first Android smartphone launched in India by HMD Global. Good 5.5 inch full HD display screen, promised timely Android updates and more importantly Nokia branding is the key strength of this smartphone.  Though it is one of the best feature-rich smartphone, it lacks in some areas. So here is the list of Nokia 6 Cons according to us.

    Nokia 6 Cons

    Nokia 6 Cons-5 Reasons not to buy

    Average performance: Indian variant of Nokia 6 is powered by Snapdragon 430 SoC with 3GB of RAM. This SoC is announced way back in September 2015. Some of the popular smartphones powered by this SoC are Redmi 3s, Redmi 3s Prime, Lenovo K6, Moto G5, Zenfone 3 Max, Zenfone 3 Laser to name few. As you might have already noticed, most of them are launched in 2016. The successor to this SoC, Snapdragon 435 powered by smartphones such as Redmi 4, LG Q6 etc are available in the market now.

    Snapdragon 430 SoC with full HD display screen, struggles a bit in performance. We have faced UI lags many times. Gaming performance on the high-end game also not impressive. Graphics-intensive games are slow and we felt regular frame drops while playing.

    No LED notification: The variant of Nokia 6 which was initially launched in China had LED notification light. The Indian variant does not features LED notification. As it does not have always on screen, it is mandatory to wake the phone to check the notifications.

    No OIS or EIS: Nokia 6 has some impressive camera hardware and intuitive camera app. However, it lacks optical image stabilization. It also misses on the electronic stabilization. Hence, while taking photos in low light, the hand should be sturdy. Otherwise, pictures will come out shaky. Even in video recording, lack of stabilization will be felt.

    Lacks fast charging support: Nokia 6 does not support fast charging. In our review period, it took close to 3 hours for full charge from zero.

    Price: Nokia 6 price in India is Rs.14,999. At this price, there are many smartphones which offer much better performance. Smartphones like  Redmi Note 4, Mi A1, Moto G5 Plus etc are available at around same price and packs better internals.

    These are the main Nokia 6 Cons according to us based on our experience with the device. Let us know your thoughts in the comment section below.

  • Mi Air Purifier 2 Long Term Review-Pros and Cons

    Mi Air Purifier 2 is a stylish compact air purifier which is priced at Rs.9,999. I have been using this air purifier at my home from December 2016. Based on my last one year’s experience,  here is the Mi Air Purifier 2 review.

    Mi Airpurfier 2 comes with triple layer 360-degree cylindrical filter.  This triple-layer design claims to removes particles larger than 0.3 microns with a high-density EPA filter, while an activated carbon filter removes formaldehyde, bad odor, and other harmful substances.

    Mi Air purifier 2

    Mi Air Purifier 2 Review- Pros and Cons

    Mi Air purifier 2 Pros

    Here are the few things which I liked with this air purifier.

    • Design: It is one of the best-looking air purifiers in the sub 10k price range.
    • Quiet operation:  In night mode, it is almost silent. Xiaomi claims it produces a noise level as low as 30db in night mode. Though I have not tested actual decibel, it is quieter than the other air purifiers I have tested so far. It is hardly audible.
    • Sync with a smartphone: This Xiaomi air purifier can be paired with the smartphone through WiFi. Using the Mi Home app, you can monitor pollution level in real time or control the air purifier.
    • Easy filter replacement: As it has a single cylindrical filter, it is very easy to change the filters.

    Mi Air purifier 2 Cons

    Over the close to one year experience with this air purifier, I felt few things could have been better. Here is the list of Mi Air Purifier cons.

    • Cost of filter: According Xiaomi, the filter should be replaced every 4 to 6 months depending on the usage. MRP of the filter is around Rs.3,000. That means for 1 year 2 filter replacements required which cost around Rs.6,000 per year. In my usage wherein I have used the purifier almost 20 plus hours a day, I was required to replace it every 3 months. Otherwise, some bad odor starts to come in from the purifier.
    • Automatic shifting to auto mode after 3 hours:  Irrespective of the mode you have selected and pollution level, this purifier automatically shifts to auto mode after 3 hours. So, even if the air pollution is high, you cannot run at high speed more than 3 hours continuously. Need to change the mode manually every 3 hours if required.
    • Filter availability: It is becoming a common problem for buying Xiaomi devices. Sometimes, filters are not available to buy. It says  Out of stock.
    • No real-time air pollution monitor:  Exact air quality can be monitored through the Mi Home App. However, if you don’t have the WiFi there is no way to see the air quality level. There is a LED indicator. But it will give only rough idea.

    Mi Air purifier 2 review summary: Priced at Rs.9,999, this air purifier offers a lot. Stylish compact design with max 310m/h Clean Air Delivery Rate (CADR) is the biggest USP. However, it lacks in few areas.