Monthly Archives: January 2014

Why do you turn off Auto Filter and then turn it on again??

I have seen many people turning OFF and then ON Auto Filter in order just to SHOW ALL values (in Excel 2003’s terms) or CLEAR all filters (in Excel 2007 or above’s terms).  Frankly, I think many people do this … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 1 Comment

Time Conversion – How to convert 24hr input as 4-digit number into TIME in Excel?

How to convert something like 1200 into 12:00? For easy sake of input, we often input 24-hr time as four digit number like 930 for 9:30, 1815 for 18:15 etc.  However, if time is input in this way in Excel, … Continue reading

Rate this:

Posted in Excel Tips, Format | Tagged , , | 12 Comments

How to lookup only partially-matched value?? – Crazy lookup!!!

This is a real workplace problem. Look at the two tables below, the task was to map the Shop ID from Table 1 (left) to Table 2 (right).  In both tables there is a common key “Shop Name”.  Sound easy? … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , , , | Leave a comment

Alternative to vlookup – Index and Match

Lookup from Right to Left In the above example, if it is given the Student ID, it would be so easy to find one’s corresponding Name and Grade by using vlookup.  However, if we would need to find out the … Continue reading

Rate this:

Posted in Formula | Tagged , , | 3 Comments

Advanced vlookup – wildcard characters “?” and “*”

Why vlookup gives me wrong answer??? Bugs in vlookup???  I am sure it’s not about text vs. number, but it still fails to give the correct answer!!! I guess many users are not aware of the wildcard characters in Excel.  … Continue reading

Rate this:

Posted in Formula | Tagged , , | 27 Comments

How much do I need to pay?

This is not an Excel question… Just had a happy dinner gathering with friends! Good food, good friend, and good price… 🙂 While a friend was calculating how much to share by each of us, others asked me how to … Continue reading

Rate this:

Posted in General | Leave a comment

Advanced vlookup – Text vs. Number

Why does vlookup not return the lookup value? I am sure it is in the source data table.  I see it by my eyes. Have you ever experienced this?? That’s normal when you import your data from other database.  Fortunately … Continue reading

Rate this:

Posted in Formula | Tagged , , | 14 Comments

vlookup – True or False??

Usually we use vlookup for answering a particular question like “How many customers we had on January 1st 2014?”  We expect an exact match and hence using FALSE as the last argument in the vlookup formula. When do we use … Continue reading

Rate this:

Posted in Formula | Tagged | 12 Comments