Tag Archives: VLOOKUP

Three different ways to do case-sensitive lookup

If you do VLOOKUP, you probably know that one of the limitations of VLOOKUP is case-insensitive.  It means case is not a consideration in the lookup process, where PETER is essentially the same as peter. Hence VLOOKUP will consider that … Continue reading

Rate this:

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

VLOOKUP the Nth matched item, with helper column

This should be the last post in 2015.  So it’s better to answer unanswered question in the year.  🙂 Question: “VLOOKUP will return the first matched value found, in case there are multiple matched records what formula should we use?” This … Continue reading

Rate this:

Posted in Formula | Tagged , | Leave a comment

Nested IF vs. VLOOKUP – Which one to use?

A well-drawn flowchart or a well-organized table helps you write a successful formula effectively Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 6 Comments

Pay attention when you concatenate – A1 & B1 vs. A1 & “|” & B1

In many case, we may want to combine texts from different cells.  This can be done by either CONCATENATE function or simply an Ampersand &.  E.g. A1 & B1 & C1 …… We may take it for granted as it looks so … Continue reading

Rate this:

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

3D VLOOKUP – Perform VLOOKUP from more than one table

Pls have your 3D glasses ready… only if you are going to watch a 3D movie.  You don’t need it for a 3D VLOOKUP. We talked about how to do a 2D VLOOKUP by using MATCH together with VLOOKUP.  With that … Continue reading

Rate this:

Posted in Formula | Tagged , , , | 3 Comments

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

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

vlookup with Match

Hard-coding the col_index_num in the vlookup formula is not a wise choice as you might have to look into the formula and make changes every time you copy and paste your formula.  An alternative approach of marking column index on … Continue reading

Rate this:

Posted in Formula | Tagged , | 8 Comments

Tips in constructing vlookup

Tips in constructing vlookup We talked about the syntax of vlookup and we had written a vlookup formula successfully in the previous post.  YEAH! Now we want to ride on the formula by copying it DOWN and to the RIGHT … Continue reading

Rate this:

Posted in Formula | Tagged | 5 Comments

The basic of vlookup

vlookup – Something you should know about The basic of vlookup Definition of LOOKUP (http://www.merriam-webster.com/dictionary/lookup) :  the process or an instance of looking something up;especially:  the process of matching by computer the words of a text with material stored in memory v, in the … Continue reading

Rate this:

Posted in Formula | Tagged | 6 Comments