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:
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.