Looks up a value in a table and returns a related value

For example, if you wanted to know the number of units of inventory held at location in Musanze:

You look up the name of the location (“Musanze”)

The number of units at the location will be returned

Very useful when comparing between two lists or
data sets

Qs: Do you use Vlookups now? Can you tell us what they do?

The vlookup will automatically find the value you’re looking for, and then return a related value

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

4 inputs, separated by commas:

lookup_value = value to be found in the first column of the table

table_array = table which contains the data

col_index_num = column number in the table from which the matching value must be returned

range_lookup = if VLOOKUP cannot find the exact value, the next largest value that is less than lookup_value is returned if it is set to “true” or left blank.  In most cases, set this to “false” to get exact numbers.

We want to figure out how much we owe Bank of Kigali

Here we want to figure out how much we owe Bank of Kigali

1 – Look up value is Bank of Kigali. It shows B4 as we’re looking up the value that is in that cell

2 – table array – highlight the whole table, as we’re looking for the payable outstanding – that needs to be highlighted

3 – column index – how many columns away from the looked up name, is the value we want – here is it 6. Make sure to include both the end columns

4 – range lookup – we want the exact value, so we say FALSE