Searching Records Using Excel Functions

This is my very first post discussing about the technical side of me. As I work on database maintenance, data manipulation, data analysis, and others on different kinds of data, it gives me an idea to share this kind of stuff.

I've been using Microsoft Office Excel for about 10 years and my knowledge about using its features as well as its functions have been improved since the very first time I work on records using the spreadsheet application.

For every moment, I tried to analyze and/or manipulate small or large amount of data, I've learned new things on how things are going on. Data analysis, more often than not, is one of the few things I am more knowledgeable. From producing relevant statistics to designing a valuable report, I am very much keen on every single detail that must be displayed in the output.

Part of such analysis is using the available Excel functions to perform computations, database management, and lookup and reference. I am very much aware that there are formulas in Excel which can be used that produces same results. Therefore, I must be very careful on the functions I choose in order to avoid mistakes.

In the part of lookup and reference, I was able to compare these two functions. The one that gave accurate results is what I used most of the time. Of course, I must note that these things always has its limitations.

Such functions are LOOKUP() and the combination of INDIRECT() and MATCH() functions.

Honestly, I'm really not a fan of using LOOKUP() function when searching for a certain data or records. Why? Because it does not give me exact results I wanted. I will give you an example, to be able to explain what I am talking about. For the detailed information about the functions I am going to use in the sample, please visit: Office.com.

Here are the basic information about the following functions and their syntax:

LOOKUP(lookup_value, lookup_vector, [result_vector])
This function returns a value either from a one-row or one-column range or from an array. This function has two syntax forms: the vector form and the array form. For more details, go to LOOKUP function.

MATCH(lookup_value, lookup_array, [match_type])
This function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For more details, go to MATCH function.

INDIRECT(ref_text, [a1])
This function returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself. For more details, go to INDIRECT function.

The table below shows 10 items with the corresponding prices. I will use the functions mentioned above to search for a certain record, say the price of item F.

Excel Sample Table

Using LOOKUP function, the formula is written as =LOOKUP("F",$A$2:$A$11,$B$2:$B$11), where 'F' is the item where its price is being seached, $A$2:$A$11 is the range where item 'F' can be found, and $B$2:$B$11 is the range where the result will come from.

Likewise, using INDIRECT and MATCH function, the formula is written as =INDIRECT("$B$"&MATCH("F",$A$2:$A$11,0)+1), where 'F' is the item where its price is being search, $A$2:$A$11 is the range where item F can be found, '0' is the match type which is equivalent to exact match. Since MATCH function returns the position of the value being searched, +1 is included in order to identify the position of the first value in the given range.

In the given table, the search will start at the second row as where the first value located. $B is the exact reference of the value being search concatenating the value return by MATCH function. Both formulas yield the same result of $7.50.

Now, let's find item with a price of $10.25.

Using LOOKUP function, the formula is written as =LOOKUP(10.25,$B$2:$B$11,$A$2:$A$11). Using INDIRECT and MATCH function, the formula is written as =INDIRECT("$A$"&MATCH(10.25,$B$2:$B$11,0)+1).

Q: Do both formulas yield the same result?
A: No. LOOKUP function resulted to item 'J', while INDIRECT and MATCH functions resulted to item 'G'.

Why?

LOOKUP function yield the result item 'J' which supposedly item 'G'. Because after three records in the 'Price' column, LOOKUP function failed to meet the criteria as it displays the last value in the given range. Having said that, it implies that LOOKUP function works perfectly for the set of records that are arranged in ascending order.

Unlike LOOKUP function, the combination of INDIRECT and MATCH functions work fittingly on the data set. The formula resulted to $A$8 which is equivalent to item 'G' with a price of $10.25. Thus, the functions doesn't concern how the records are arranged. In the given data set, it searches the specific record required by identifying the exact placement of the record searched.

That is why, as I am working on large amount of data most of the time, I always consider these things:

• Nature of the data. Simply what is the data all about, whether it is a survey, profile, and etc.
• Structure of the data. How the data are setup in a certain database or spreadsheet.
• Requirements. What is asked or needed to pull off based on the pool of records.
• Formulas to be used. Tools used in order to get the specific results from the given data set. SQL, if using Access database.

All formulas given has its own uses and functions and try not to forget its limitations. The proper and excellent choice and usage of the formula in order to create a meaningful result is a must.

Share this:
Facebook Twitter Google+ LinkedIn Technorati Tumblr StumbleUpon Delicious Reddit Digg Newsvine
computation, data analysis, database, excel formula, excel function, records, The Hawk-Eye, tutorial, Rate this post: {[['']]}	Like it?