Vlookup and Hlookup are two Excel functions that scan through data in a grid or a table and identify and instance of data that matches the information you entered in the function. Vlookup stands for “vertical lookup” and hlookup stands for “horizontal lookup.” You can combine these two functions in a formula.
- Skill level:
Open Excel and prepare source data. Your table should include column headings and include several rows. Create a rudimentary stock tracking application. Create column headings for “Invoice Number,” “Invoice Date,” “Product ID,” “Product Name,” “Price,” “Total” and “Stock Alert.” Widen each column to see the full title. Enter ten rows of data to have a base of data to test the formula. Enter 10010 in cell A2 as the first invoice number and press enter. Click on the small square at the bottom right hand corner of the frame around A2 and drag it down to A11. Enter a date in B2 and drag the frame around that cell to reach B11. These two columns are not important for the demonstration of the formula. Enter a number between 1 and 4 in the Product ID column. You will have to repeat each number several times, which is OK. You will have titles in cells A1 to H1 and the data rows will extend down to fill cells A11 to H11.
Create a static table for source data. Enter in cells K1 to K3 the headings “Product ID,” “Product Name” and “Price”. Fill in four records in the table numbering the products 1, 2, 3 and 4. Call them “Big Widget,” “Medium Widget,” “Small Widget” and “Widget Pack.” Give them prices of 200, 150, 100 and 250 respectively. You will notice that this table is in a horizontal format.
Create a second table for stock levels. Enter “Product ID” in cell K7 and “Starting Stock” in cell L7. Enter the numbers 1 to 4 in the K column and put stock levels at 2, 3, 7 and 4. This table is in a vertical format.
Populate values for Product Name and Price in the Invoice table. Enter the following formula in cell D2: “=HLOOKUP(C2,$K$1:$O$4,2,FALSE)” and in E2: “=HLOOKUP(C2,$K$1:$O$4,3,FALSE)” without the quotes in both cases. Click in D2 and drag a frame across to E2. Let go of the mouse button. Click the small square in the bottom right corner of the frame and drag it down to extend to E11. This will populate all the Product Name and Price columns by reference to the Product look-up table.
Populate the Quantity column with random numbers. Make sure that the first few entries for each product ID are below the stock levels you recorded in the Stock look-up table. Enter the formula “=E2*F2” in cell G2. Drag the frame around this cell down to cell G11 to populate this column.
Enter the following formula in cell H2: “=IF(SUMIF($C$2:C2,C2,$F$2:F2)>VLOOKUP(C2,$K$7:$L$11,2,FALSE),CONCATENATE("Stock Alert for ", HLOOKUP(C2,$K$1:$O$3,2,FALSE)),"")” this formula contains both the vlookpu function and the hlookup function because it needs to reference data from both the look-up tables in the sheet. Click on the square at the bottom right corner of H2 and drag the frame down to H11. You now have a rudimentary stock alert system combining vlookup and hlookup in the same formula.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for