Friday, 4 January 2019

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.

No comments:

Post a Comment