Super Charge Your Projects!!

~ Dirty tricks to increase speed, efficiency, and productivity in your MS Office projects ~

Super Charge Your Projects!! - ~ Dirty tricks to increase speed, efficiency, and productivity in your MS Office projects ~

VLOOKUP: Get a value from a different report!

 

Function
=VLOOKUP(lookup_value,table_array,col_index_num)

3 Parameters
lookup_value = the value to look up (e.g. reference number/text)
table_array = the source table to lookup
col_index_num = column number where the information is in

CAVEATS

Lookup Value & Table Array
The 1st column MUST be the column where the lookup value is. E.g.) If your lookup value is a site number, the 1st column of the Table Array MUST be a site number column
The format of the Lookup Value and the 1st column of the Table Array MUST be the same. Note: some numbers may be stored as text if the VLOOKUP function fails (“#N/A”)

VLOOKUP is a function that’s useful to get data from another report (or worksheet) into another. For instance, you may have a list of sites by their site numbers, but it does not have a country column that you want. In this case, you can use the VLOOKUP function to pull the country information from a different report (worksheet) to the report that you want.

Index & Match Functions – A More Flexible VLOOKUP!!

MATCH FUNCTION

INDEX FUNCTION

MATCH FUNCTION

Function: a lookup function that returns the relative position of an item in a list

=MATCH(lookup_value,lookup_array,match_type)

Parameters: 3

  • lookup_value = the value you use to find the value you want in a table.
  • lookup_array = contiguous range of cells containing possible lookup values.
  • match_type = tells index which row to look at.

0 = Exact Match

1 = Approximate Match (less than the lookup_value)

-1 = Approximate Match (greater than the lookup_value)

INDEX FUNCTION

Function: lookup function that can do a two-way lookup.

=INDEX(array,row_num,column_num)

Parameters: 3

  • array = the two dementioanl table
  • row_num= tells index which row to look at
  • column_num = tells index which row to look at