Rearrange data using VLOOKUP excel

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. This function is lookup a value in a table by matching on the first column. The syntax of the function as follows:

=VLOOKUP (value, table, col_index, [range_lookup])

value – The value to look for in the first column of a table.
table – The table from which to retrieve a value.
col_index – The column in the table from which to retrieve a value.
range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.

It is very useful, especially in hydrology when you need to rearrange the data based on something (For example, when rearrange rainfall/discharge time series). Here some example:

1- We want to find the state for each station ID (right side of the picture) based on the list of detailed station (left side).

2- Just use and type of the function.

=VLOOKUP (value, table, col_index, [range_lookup])

value = station ID ( The value to look for)
table = list of detailed station (the table from which to retrieve a value).
col_index = no of column. In here, we want to retrieve a value from the state column.
range_lookup = TRUE (approximate match)

3 – You can retrieve the value after completing the formula. Simple! You can complete the rest of the empty cell.


About zulkarnainh

I was born in Melaka (1987), holds the degree in B.Eng.(2010), M.Eng (2012) and Ph.D. (2016) in Civil Engineering at Universiti Teknologi Malaysia. I have been the UniMAP since 2016 and currently serving as Senior Lecturer. Feel free to contact me if you are interested to collaborate or pursuit a study (Master or Ph.D.) with me. Thank you!
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

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