Excel - Vertical- and horizontal Lookup

To introduction

When working with Excel sooner or later there will be a need for getting data from a list. The Excel functions HLOOKUP and VLOOKUP can be used for this. With these functions you can get a value from a list on basis of a searchvalue in the most left column, or from the upper row, of the list.
Perfect for a fixed list, but when you want to search in a list that often changes the problems begin. You have to enter a number to locate the column in which you want to search !

The next functions enable you to search on a value in both the left column and top row.

An example for using VHLOOKUP() :

In cell H5 we want to retrieve the kind of article client John bought. Instead of using an indexnumber for the column you just use the columnheader 'Article'.

=VHLOOKUP(A2:E10,G5,H3)


=INDEX(A2:E10,MATCH(G5,A2:A10,0),MATCH(H3,A2:E2,0))

To use the VHLOOKUP() function you have to save the code below to a VBA module of your workbook, act as follows :
- go to VBA, <Alt><F11>
- make a module, select the VBAProject of the workbook, rightermouseclick, Insert, Module
- dubbleclick the module you made
- copy the code to the module.