Vlookup in Microsoft Excel

Vlookup behavior is a little strange in MS excel,

Here is a sample excel (as a google spreadsheet): Click on the vlookup tab. (Forget the other tabs)

https://docs.google.com/spreadsheets/d/1BEuDAjO0xTjr8zJ9FPjB2ZlMUZSjNOuR5uN6D5hP_C8/edit?usp=sharing

An array with 2 columns is shown in $A2:$B7, Here I want to pick the value from column B when column A = Y. Simple?

Now look at cells E4 and E5, Both use vlookup to get the data from 2nd column when 1st column is Y.

E4 uses an approximate match and E5 uses the exact match (note the change in the last parameter in the two vlookup formula). 

Now I always assume that approximate match is used only when there is NO exact match. But then that is not the case. We find even when there is an exact match, the approximate algorithm overrides. And of course the approximate match expects the first column to be sorted which it isn't - column A isn't sorted.

Now I copy the same values into columns H and I and then sort column H. And now the cells L4, L5 both show the correct value.

The learning:

When you are ONLY looking for an exact match SET the 4th parameter to 0 without fail.

Incidentally, if you go to the "Filtered data" tab, you will see red filled cells F4:I6 showing the use of standard functions such as sum, max, stdev using filtered data. 

And red filled cell C11 shows the use of sumproduct having filters on rows AND columns. The data array is in italics. This is a complex condition which cannot be solved using  SUMIF. SUMIF can sum with filters on either rows OR columns but not both.
Let's go through the formula in cell C11.

=SUMPRODUCT((C4:D6)*(G4:H6)*(A4:A6>=2)*(B4:B6>=4)*(C3:D3="C")) 

The brackets that have equality or inequality act as filters. What we are trying to find out is:  

SUMPRODUCT((C4:D6)*(G4:H6)) subject to the three filters.  

The last filter (C3:D3="C") results in only column C being true, since D3 is blank. 

Now the SUMPRODUCT reduces to:
SUMPRODUCT((C4:C6)*(G4:G6))  

Now
(A4:A6>=2)*(B4:B6>=4) results in only the 6th row being true. The filter on column A eliminates the 4th row. The additional filter on column B removes the 5th row. Each of the filter acts as an AND.

Hence our final SUMPRODUCT reduces to:SUMPRODUCT((C6:C6)*(G6:G6)) = c6*G6= 23*3 = 69.

See how the filters eliminated rows and columns from the calculations. This is a very useful too and should be taught in intermediate or advanced classes of Excel.