Search rows in Excel, how does it work? With the function HLOOKUP, this feature looks as opposed to VLOOKUP  a matrix line by line and then outputs the value in the column. Using a practical example, you can clearly see the advantage of a HLOOKUP.

 The following figure shows an example table with companies A and B with sales figures of 10 years. We would now like to click on a year z. B. receive the sales figures of company A and B for 2009.

To get the sales of company A and B we use the HLOOKUP function, as the algorithm searches line by line. The prerequisite is that A and B are next to each other. The first HLOOKUP is for A and the second is for B, as shown in the two following figures.

 

The expression or parameter of the function begins with the search criterion, then comes the matrix (area), the line in which the value is in the example F1 and then whether it should be clear.

HLOOKUP(Search criterion;Matrix;Line index;Area_reference)

Here is an example of the evaluation area:

The formulas are in the cells under A and B.

 

To provide a useful example, we will add one to the function Drop-down list with the years. To do this, we proceed as follows:

Cursor on G2! then on data > Data verification

 

Under data verification on Settings > Allow > List > Which is the area with the years> OK.

 

Then in G2 following figure should be available.

But now we have one more requirement, if the year is selected, the HLOOKUP function must be passed the correct line. We can do that with a few little tricks. The line always starts with two because one is the line with A and B. Knowing this, we simply have to subtract the year with a number that is smaller than the first year 1998 in our example. The formula on the line F1 is = G2-1998

Have fun trying.

___________________________________________________
This tip comes from www.win-tipps-tweaks.de
© Copyright Michael Hille

Warning:
Using Registry Editor or its tips incorrectly can cause serious system problems that may require you to reinstall your operating system. Tampering with the registry files and using the tips is at your own risk.