Tag Archives: INDEX

Fixing inconsistent data type in lookup table in #Excel

Posted on December 11, 2021 by MF

A common VLOOKUP problem with an easy fix The situation – VLOOKUP fails… Have you ever encountered something like this? This is quite common. We are sure that the VLOOKUP formula is correct. We are sure that the lookup values … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , | 2 Comments

Be cautious when using XLOOKUP

Posted on November 30, 2019 by MF

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

How to lookup an image in #Excel

Posted on January 20, 2018 by MF

Lookup an image using “Linked Picture” in #Excel Perhaps you have used VLOOKUP to return a value from a table very often.  However you cannot use VLOOKUP to return an image…  So how the above can be done?  You will … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , , | 20 Comments

Lookup a specific combination of characters exclusively, e.g. 5A not 5AA

Posted on September 10, 2017 by MF

Got the following question: hi, how do i search for a specific combination of characters exclusively i.e. if one cell contains 5A;3B&4C while another cell contains 5AA;3B&4C, and my search criteria is 5A, i want the results to return only … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , | 3 Comments

Interactive Chart is not difficult to make – Part 2/3

Posted on July 22, 2017 by MF

Using Scroll Bar to show more This is an extension of the previous post – Using Checkbox to highlight negative growth in a bar chart.  In that post, we had created a chart showing top 10 items, which allowed user … Continue reading →

Rate this:

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

Interactive Chart with navigation panel [NO VBA]

Posted on March 4, 2017 by MF

Isn’t it nice?  Don’t think that this is difficult.  No VBA is required.  Indeed you only need to know a few Excel skills in order to create an interactive chart  with navigation panel like this. The skills required: Conditional formatting … Continue reading →

Rate this:

Posted in Chart | Tagged , , , , , | 3 Comments

Want some pies?

Posted on February 25, 2017 by MF

First of all, this blogpost is about Pie Chart, but not about how to make a Pie Chart.  Indeed, I am not going to show the steps of making Pie Chart here.   Also I am not intended to discuss whether … Continue reading →

Rate this:

Posted in Chart | Tagged , , | 2 Comments

Three different ways to do case-sensitive lookup

Posted on April 9, 2016 by MF

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 , , , , , , | 3 Comments

2D SUMIF with two variables – one on column and one on row

Posted on September 20, 2014 by MF

SUMIF is a commonly used function to give you sum of a range that meets a specific criterion.  For the example above, if you want to know the total expense of Department 1 in Jan, you can use SUMIF: The Syntax … Continue reading →

Rate this:

Posted in Formula | Tagged , , , | 12 Comments

Perform VLOOKUP with 2 lookup values

Posted on May 11, 2014 by MF

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

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

Posted on January 23, 2014 by MF

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

Posted on January 16, 2014 by MF

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