Tag Archives: INDEX

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

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 , , , , | 1 Comment

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

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]

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 , , , , , | Leave a comment

Want some pies?

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

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

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

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