Lookup and Reference Functions – That Helped Me Keep My Job

Share Now


Vlookup Function

Stands for vertically lookup from the leftmost column of the table, its look for the data in the same row from a column you specify.

Syntax for the Vlookup function:

=Vlookup(lookup value, table array, column index number, [range lookup])

Lookup value is the value we need to look on for more information. Lookup value is in the first column of the table array.

Table array is the two or more columns dataset, where your return value exists.

Column index number is the column number from which you want to pull back your data from. The first column is 1 and so on.

[Range lookup] is optional. We need to enter False or True. False is to find and exact match and true is to find an approximate match.

In place of False you can write 0 and True to 1. If you leave this parameter vlookup will return approximate match value.


In the above screen shot in cell “F2” Vlookup function would return $ 41000 for EmpID 11004(cell c2).

Lookup Function

The LOOKUP function looks for the value in the lookup_range and returns the value from the same position from the result_range.

Note: lookup_function cannot find an exact match, it select the largest value in the lookup range.


Lookup function

Match Function:

Match function returns the position of a value in an array.

Syntax of Match Function

=Match(lookup_value, lookup_array, [match_type])

lookup_value = The value you want to find in the list.

lookup_array = List in which you want to find the value.

[match_type] = Match type can be -1, 0 or 1.

-1 finds the smallest value greater than or equal to look up value.

0 finds the exact equal value to lookup value.

1 find the largest value less than or equal to lookup value.

match function in excel - lookup function

In the above screen shot, Match function would return 3 in the Cell C4. 3rd is the position of Gurgaon (lookup value) in the list B7:B13.

Index Function:

Index function returns the value from the specific position in a table or range.

Syntax of Index Function

=Index(array, row_num, [column_num])

array = Range of cells or table.

row_num = The number of rows in the array to return the value.

[column_num] = Column number is optional. The number of columns in the table to return the value.


In the above screen shot, Index Function in Cell F2 would return
=Index(B4:F4,3,3) – “Anshul Gupta”(Would return the cell D6 value)

Offset Function

Offset function returns the reference to a range that is a given number of rows and columns from the given reference. Offset have five parameters. It also helps us to create dynamic ranges with the help of 4th and 5th parameter.

The syntax of Offset function

=Offset(Reference, Rows, Columns,[ Height], [Width])

Reference – Reference is any starting cell from where the offset will be applied.

Rows – The number of rows move down/up from the reference cell in the range.

Columns – The number of columns move Right/left from the reference cell in the range.

[Height] – The number of rows in the range. This is optional; if we leave this parameter then height will be a single cell.

[Width] – The number of columns in the range. This is optional; if we leave this parameter then height will be a single cell.


Based on the above example, offset function in cell F2 would return

=offset(B4,3,2) – “Yash Pal”(Would return the cell D7 value)

Share Now
November 15, 2019

0 responses on "Lookup and Reference Functions - That Helped Me Keep My Job"

    Leave a Message

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

    Social Media

    facebook page EC Analytics Consulting   Linkedin Page EC Analytics Consulting   Youtube EC Analytics Consulting


    0124- 4601426


    EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

    EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED