Home » How To » Excel Functions » Excel Lookup Related Functions: VLOOKUP, HLOOKUP, MATCH, INDEX [+ Video]

Excel Lookup Related Functions: VLOOKUP, HLOOKUP, MATCH, INDEX [+ Video]

Have you got stuck with the lookup related functions in the excel and you end up working with manual work. Then you will find this post related to lookup and reference functions very useful.

I know, usually you get busy with other project related work and that’s why you don’t get much time to learn simple yet extremely useful excel functions. But you will realise, how easy are these once you get a hang of it and I am sure, you will never stop them using. I personally find them time savers.



In my earlier post, I had explained some of the string or text related excel functions and pivot tables, here I will cover most frequently used lookup and reference functions along with the video tutorials-

VLookup, HLookup, Match and Index.

1. Vlookup

Use:

Vlookup function helps you search for a value in one table in another table or array.

Syntax:

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

Parameters:

Lookup_value– Value which you want to match with table the array

Table_array – Reference table in which you want to search lookup_value above

Col_index_num– Column number from which value should be returned.

Range_lookup – Find the exact or approximate match

Consideration:




If you don’t use correct no. of columns then you will get #VALUE! or  #REF! error value depending on no. of columns you have mentioned.

Example-

=Vlookup(A5, G4:H14, 2, false)

I have covered same example in below video.

2. Hlookup

Use:

Vlookup function helps you search for a value in one table in another table or array.

Syntax:

HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Paramters:

Lookup value- Value which you want to search in first row of the another table array.

Table_array – Reference table in which you want to search lookup value above

Col_index_num- Column number from which value should be returned.

Range_lookup – Find the exact or approximate match

Example:

I have covered same example in the video.

=HLookup(A5, E4:O14, 2, false)

3. MATCH

Use:

Match function helps you search the position of the item in the range or table array

Syntax:

MATCH(lookup_value, lookup_array, [match_type])

Parameters:

lookup_value: Value you want to search/match in the range or table array

lookup_array: Range of cells where lookup_value above needs to be searched.

Example:

=MATCH(A2,G2:G11,0)

4. Index




Use:

Returns the value a table array referencing rows and columns mentioned in function

Syntax:

INDEX(array, row_num, [column_num])

Parameters:

Array:The array from which you want to pull the value

Row_num: Reference Array above with row_num mentioned

column_num: optional. Similar to row, column value is returned if mentioned.

Example:

=index(H5:H11,A5)

If you have been using any other functions or tools which you think should be shared with a tutorial with other readers, then do let me know using “reply” form below.

Leave a Reply

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

*