How to Combine Vlookup and Hlookupby Gissimee Doe
You can combine VLOOKUP and HLOOKUP, table lookup functions in Microsoft Excel, to perform complex lookups against multiple tables oriented vertically and horizontally. You use these two functions to look up related values in a table within a spreadsheet application. When you supply parameters to the lookup function, it uses those values to search for a match within a range of values in a table. Then it returns a corresponding value when a match is found. You use VLOOKUP to find values in a vertical list, when the values in your table are oriented from top to bottom, and you use HLOOKUP for horizontal searches, when your table's values are oriented left to right.
Navigate to office.microsoft.com/en-us/excel-help/redir/XT001091120.aspx?CTT=5&origin=HA001056320
Follow the instructions to download and install the “Lookup Function Sample Data” for Microsoft Excel.
Double-click on the “LookupFunctions.xls” file in your target folder to launch the sample data file in the Excel application.
Click on the “Pages” tab to select the “Pages” worksheet and copy the data in cells “A1:B39” by selecting the cells, and then simultaneously pressing the “Ctrl” and “C” keys on your keyboard.
Right-click cell “D2”on the “Pages” worksheet and select “Paste Special” from the menu.
Select the “transpose” option, and then click “OK” to create a horizontally ordered table for performing your HLOOKUP function.
Select the values from cells “D3” to “D7” and copy them by simultaneously pressing the “Ctrl” and “C” keys on your keyboard.
Click in cell “D8” and type the word “Country.” Type “UK”,”US” and “CA” in the adjoining cells “E8”, “F8” and “G8” respectively. This will create a new horizontal table in cells “D7” to “G8”, which you will use in your combined HLOOKUP and VLOOKUP formula.
Click on the “Page Views” tab to switch to the “Page Views” worksheet.
Create a results table by typing “Page ID” in cell K3, “Page Name” in cell K4 and “Country” in cell K5.
Type “=A4” in cell L3 to pass the top page ID to the look up functions.
Perform a VLOOKUP on the page ID by typing the following formula into cell “L4”: =VLOOKUP(L3,Pages!A2:B39,2,FALSE) This will return a value of “Home Page.”
Combine the VLOOKUP and HLOOKUP functions by typing the following formula into cell L5, to return the value for “Country” that matches “Home Page:” =HLOOKUP(VLOOKUP(L3,Pages!A2:B39,2,FALSE),Pages!D7:G8,2)
- Microsoft Office :“ Use HLookup and VLookup functions to find records in large worksheets”; Colin Wilcox;2011
- MSDN:” Use Worksheet Functions to Perform 2-D Table Lookups”; Sean Kavanagh;2001
- University of Wisconsin: “Excel - Using the VLOOKUP and HLOOKUP Functions”; John S.;2002
- GR Business Process Solutions:” Excel lookup functions explained”; Ray Blake
- Stockbyte/Stockbyte/Getty Images