Tag Archives: INDEX

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 , , , | 6 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