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)
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.