A20.3 Excel: Lookup - LUPMISManual

Go to content

Main menu:

Annexes 11-20 > A20. Various IT advices

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. C
opy 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:

=IF(AND(LEN(A3),COUNTIF(F$6:F$176,A3)),VLOOKUP(A3,F$6:G$176,2,0),"")
The red bold parts have been added to the original formula. Observe, that only one column is referred in the COUNTIF call. 


   

 
Back to content | Back to main menu