Main menu:
A20.3 Excel: Lookup
Level of expertise required for this Chapter: Intermediate
For report and table production, it might be necessary to link different tables from the GIS in a spreadsheet. Excel offers a range of 'lookup' functions: 'VLookup' (Vertical Lookup) is explained here.
1. Situation: You have a spreadsheet with values, where you want to have the lookup values next to them:
The reference table can look like below (example: District codes and names). See Chapter 5.6 ('Export tabular GIS data') for compiling this listing.
2. Sort this reference table: Select all values (2 columns) > Data > Sort > Sort by: Select left (reference value) column > Order: Smallest to Largest > OK
3. Link top cell: Select cell right of top first original cell [1 in screen shot below] > Press fx > In Search field: Type VLOOKUP [3] > Go [4] > Select VLOOKUP [5] > OK [6] >
> Lookup_value: Click in empty line next to 'Lookup_value' [1 in screen shot below] > Select top first cell [2] >
> Table_array: Click in empty line next to 'Table_array' [3] > Select entire entire array of reference set with 2 columns (which was just sorted) [4] >
> Col_index_num: In empty line next to 'Col_index_num': Enter '2' (for second column of this array) [5] >
> Range_lookup: In empty line next to 'Range_lookup': Enter 'false' [6] > OK [7]
The first cell should get the reference value.
4. Insert $ (row fixer) to all row references in this first cell:
5. Copy this cell all the way down. Finished.
For Experts: If you have an empty reference, it will return #N/A. To avoid this and have an empty (text) field, you have to modify the reference formula to: