Excel Vlookup And Hlookup Tutorial Pdf

Now we will create the vlookup formula that will translate the a pcode in cell c2 to the description found from the party codes worksheet.

Excel vlookup and hlookup tutorial pdf. Vlookup and hlookup are two of the most popular formulas in excel and using them together is one of the first formula combinations that people learn. So with vlookup we highlight our tablearray by columns. For example look at the below data. Vlookup relies on 4 function arguments which are numbered above.

Hlookup examples in excel. This is the master data you have. The returned value by hlookup is 5 because hlookup searches for 2016 in the range b3e4 in row 2. Ms excel has large number of functions and utilities which an average user forget to use them.

Click on the row 1 designator at the left. This function retrieves data from the table horizontally. Hlookup is a lookup and reference function. Despite it i think its worth it to know hlookup.

Remember the lookup value must be in both worksheetsfiles and for hlookup it must be the first row in the array. Figure 3 result of the vlookup and hlookup functions. Vlookup formula in excel with example pdf vlookup tutorial. In this formula the horizontal value 2016 is looked up with the hlookup section of the formula.

Vlookupg3a5e10hlookupg4b3e42falsefalse we will press the enter key. Where the vlookup function scans vertically down a column the hlookup function scans horizontally across a row. Here are some examples of the hlookup function in excel. Of course the data must be set up in an appropriate manner.

While using index match for vertical lookups and index match match for matrix style lookups are superior approaches its still a good idea to learn this formula combination and add it to your toolkit of lookup approaches. Assume you are working in an hr department and you are dealing with employees information like salary doj etc. The hlookup function searches for a value in the top row of a table or an array of values and then returns a value in the same column from a row you specify in the table or array. How to use vlookup in excel 6 7.

In hlookup we are going to do it by rows. The function must be written in a specific format so that it works correctly. The last item rangelookup 4 is optional and can be blank. Hlookup works similarly to the vlookup function.

In this example the lookupvalue happens to be in the first row of the worksheet.