Tag Archives: MATCH

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

Extract FirstLast names in CamelCase with #Excel formula

Posted on March 10, 2019 by MF

In the previous blog post, we see how Flash Fill extracts First Name and Last Name from an email address in a format shown above.  I’ve also recorded a video for that post. Flash Fill is so smart to detect … Continue reading →

Rate this:

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

Dynamic Shrinking Dropdown with Dynamic Arrays in #Excel 365

Posted on November 14, 2018 by MF

The recent HOT topics about Excel should be the new Dynamic Arrays!   After watching all the amazing demonstrations about the new Dynamic Arrays, I’ve decided to sign up to the Office Insider (Fast) programme (for Office 365 subscriber only).  … Continue reading →

Rate this:

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

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

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

Nested IF vs. VLOOKUP – Which one to use?

Posted on November 14, 2015 by MF

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

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

vlookup with Match

Posted on December 31, 2013 by MF

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