Infolinks

A function that can compare a given cell to the another cell or table cell with corresponding values to return an immediately value in Excel 2007
by: Revengsky Joseph D. Reyes (a.k.a rjdreyes – jcyberinux.com)

Open Microsoft Excel 2007 Spreadsheet.

In order to make that happen, there are two methods: (which is I think, so far, is the fastest way)

1. VLOOKUP

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 V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

SYNTAX : VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value – The value to search in the first column of the table array. Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Table_array – Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

Col_index_num – The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

Range_lookup – A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will only find an exact match.

EXAMPLE : VLOOKUP(A2,$E$2:$F$5,2,FALSE)

Using VLOOKUP and HLOOKUP in Microsoft Excel

A2 cell – the cell is a reference or a given input for the lookup value

B2 cell – the cell is the value of the reference or the output comparison to the given table cell.

$E$2 cell – were the starting point, it is usually the reference or the comparison in the given input of lookup value

$F$5 cell – were the ending point, it is usually the value corresponding on the table array reference. Were as when the lookup value found a match, on the reference, this is the given value or output.

2 – the index value that should be return, if the value is 1, it will return the reference itself, while 2, it will return the given output for the reference, and so on.

FALSE – only the exact match should be lookup, otherwise not found.

For more info on VLOOKUP click here…

2. HLOOKUP

Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The H in HLOOKUP stands for “Horizontal.”

SYNTAX : HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Lookup_value – is the value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string.

Table_array – is a table of information in which data is looked up. Use a reference to a range or a range name. The values in the first row of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

Row_index_num – is the row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on.

Range_lookup – is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

EXAMPLE : VLOOKUP(A2,$D$2:$G$3,2,FALSE)

Using VLOOKUP and HLOOKUP in Microsoft Excel

A2 cell – the cell is a reference or a given input for the lookup value

B2 cell – the cell is the value of the reference or the output comparison to the given table cell.

$D$2 cell – were the starting point, it is usually the reference or the comparison in the given input of lookup value

$G$3 cell – were the ending point, it is usually the value corresponding on the table array reference. Were as when the lookup value found a match, on the reference, this is the given value or output.

2 – the index value that should be return, if the value is 1, it will return the reference itself, while 2, it will return the given output for the reference, and so on.

FALSE – only the exact match should be lookup, otherwise not found.

For more info on HLOOKUP click here…

 

I hope this tutorials helps you out! 😀

Related References / Links / Articles :
VLOOKUP – Excel – Microsoft Office.com – Official
HLOOKUP – Excel – Microsoft Office.com – Official

Related Posts Plugin for WordPress, Blogger...
Share
Infolinks