EASY-to-follow EXCEL TUTORIALS

How does the Excel VLOOKUP function work?

In the previous post (about LOOKUP function), I mentioned VLOOKUP but did not really go into details. I'll go into details this time but I'll make this VLOOKUP tutorial quick and simple yet understandable.

The V in VLOOKUP stands for vertical--not value! It searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. The syntax for this function is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).

In Excel, the LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function has 2 different syntaxes, namely: vector and array

The LOOKUP vector syntax is Lookup(lookup_value,lookup_vector,result_vector). This form of LOOKUP looks in a one-row or one-column range for a value,then returns a value from the same position in a second one-row or one-column range.

Timestamps in excel cells can just be ignored if they don't affect your formula. Don't get it?

Illustration: You are trying to find out using excel conditional formula which cells have values ess than or equal a particular date. A1 which contains 1/1/11 equals A2 which contains 1/1/11 (=A1=A2) returns False, meaning the values you are trying to compare are not equal. Weird. The result of the formula should have been True because are just the same. Not necessarily.

One or both of those cells might contain a timestamp. How a cell value appears depends on its formatting. So if the format was m/d/yy then, naturally, you would see 1/1/2011. The formula bar will show what a cell really contains.

Now, change the format of those cell to numbers. A1 will show 40544.98 and will just show 40544. So, technically, they are not equal. To remove the decimal values, use excel int function which leaves just the whole numbers.

=int(A1)=int(A2) should result to true.

You may think asking the question "WHAT IS EXCEL?" is pretty stupid. But think again. Even those who have been using this Microsoft spreadsheet application and acquired a doctorate degree in dealing with excel formula and functions, pivot tables and VBA (Visual Basic for Application) aka macro can't give a brief yet concise description of EXCEL. I'm not generalizing but most just really can't!