Search Results for: vlookup

#PowerQuery makes advanced VLOOKUP so easy!

Do you use VLOOKUP?  If you do, you should know there are limitations to VLOOKUP.  Also, there are some cases VLOOKUP is not straightforward and efficient. Consider the following cases: What we want to achieve is to map the corresponding … Continue reading

Rate this:

Posted in Power Query | Tagged , | 1 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

HLOOKUP – The less-know sibling of VLOOKUP

Many people talk about VLOOKUP and agree that it is one of the most useful functions in their work life with Excel.  However, if you ask them about HLOOKUP, maybe 30% of them have no idea of its existence.  The Google search … Continue reading

Rate this:

Posted in Formula | Tagged | 2 Comments

Perform VLOOKUP with 2 lookup values

4 different ways to perform LOOKUP with 2 lookup values We know that VLOOKUP is very useful. At the same time, we know that VLOOKUP has its limitations. E.g. VLOOKUP only looks from left to right; VLOOKUP only handle one … Continue reading

Rate this:

Posted in Formula | Tagged , , , , | 38 Comments

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 | 10 Comments

Have you encountered any weird formula in #Excel?

Happy Halloween! 👻 Let’s do something fun. You are invited to leave your comments below. For what? For sharing with us any “weird” formula that you have ever seen in Excel. Real cases please. 😁 Wait… what do I mean … Continue reading

Rate this:

Posted in Formula | Tagged | 1 Comment

#Excel Power Query solves a real life problem – Who eats what?

Interesting application of Power Query for real life problem – See how to transform table into different layout using Power Query I came across this Power Query Challenge from Computergaga and I found it really interesting as it seems that … Continue reading

Rate this:

Posted in Power Query | Tagged , , , , , | 7 Comments

Be cautious when using XLOOKUP

Can you spot the error in the above formula? It’s not about the new function of XLOOKUP.  It’s about inevitable human error… If you are an Excel fan, you should be aware of the exciting XLOOKUP function in Office 365.  … Continue reading

Rate this:

Posted in Formula | Tagged , , , | 5 Comments

Suggestion: Adding a button for number formats in thousand/million/billion in #Excel

Did you know what you may submit your ideas to improve Excel through UserVoice? The recent HOT news in the Excel community – the new XLOOKUP function is also resulted from an idea submitted in UserVoice. While everyone is super exciting … Continue reading

Rate this:

Posted in General | Tagged | Leave a comment

Hard-coded range expands automatically with #Excel Table

The INDIRECT trick of using Structured Reference in Conditional Formatting   Is it a feature or a bug? This is an extension of the previous post, in which we discussed the use of INDIRECT trick to deal with Excel Table in Conditional … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 1 Comment

Learn something new from something that I think I knew well…

Today is a public holiday in Hong Kong.  A perfect break in the middle of a busy week.  What did I do on a public holiday?  Excel, Excel and Excel. 🙂 I watched a few videos from my favourite YouTube … Continue reading

Rate this:

Posted in Formula | Tagged , | Leave a comment

No more Copy and Paste to combine tables with #PowerQuery

Basic of Append Query in #PowerQuery With no doubt, combining multiple tables (mostly on different worksheets, or even in different files) into a single “master” table for further analyses is one of the most tedious tasks we deal with Excel … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment