3 most useful Lookup function to easily fetch data

image_pdf

1. How to use VLOOKUP function?

The Excel VLOOKUP function also stands for "Vertical Lookup". It 'looks up' for a given value in the column in the data array and returns the corresponding value of another column in the data array.

Syntax:

=VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

Where lookup_value: The value that you want to look for in the column of the supplied data array.

table_array: The data array or table that you want to search for the supplied lookup_value.

col_index_num: The column number from which you want to return the corresponding value.

[range_lookup]: An optional argument which can be set either TRUE or FALSE. TRUE if we don't want the exact match to the supplied lookup_value. False if we want to find the exact match to the supplied lookup_value.

Example 1:

The following examples shows the use of the Vlookup function. Here we have used Range lookup as TRUE to tell computer that we want the approximate value and exact match is not required.

vlookup1

Example 2: False represents that we want the exact match. 

vlookup

2. How to use HLOOKUP function?

The Excel Hlookup function also stands for "Horizontal Lookup". It looks up for a given value in the row in the data array and returns the corresponding value of another row in the data array.

Syntax:

=HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Where lookup_value: The value that you want to look for in the row of the supplied data array.

table_array: The data array or table that you want to search for the supplied lookup_value.

row_index_num: The row number from which you want to return the corresponding value.

[range_lookup]: An optional argument which can be set either TRUE or FALSE. TRUE if we don't want the exact match to the supplied lookup_value. False if we want to find the exact match to the supplied lookup_value.

Related Link: Check out how we can Sum specific cells based on condition

Example 1:

The following examples shows the use of the HLookup function. In simple words following formula check marks of Julia(A7) from third row(3), which is marks of Science.

hlookup

Example 2: Let's find out what is your rating if you have 80 marks. HLOOKUP looksup the value in B1:F3 and provide the result from third row(3). Here it provides us the approximate match.

hlookup1

3. How to use the INDEX function?

The Array format of the Index function is the most basic form of the function, and is used when you want to look up a reference to a cell within a single range. It returns a cell value from specified Row or Column within a single range.

Syntax:

=INDEX( array, row_num, col_num )

Where array: The specified range of cells.

row_num: Denotes the row number of the specified data range.

col_num: Denotes the column number of the specified data range.

Example:

Here the reference function returns reference to row 5.

index

Example: Here the index function returns a reference to row 3 and column 2.

index1

Explore more, It's Amazing…

Use Conditional Formatting to change the cell color based on criteria

100 Most Useful Keyboard Shortcuts of MS Excel

For any kind of queries on MS Excel, please feel free to Contact us.

Stay tuned with us on www.facebook.com/amazingxcel for more amazing tips on MS Excel.

2,460 total views, 3 views today

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>