In: Accounting
discussing advanced functions in Excel (finance functions, If, nested If, Vlookup, Hlookup, etc.). Please research additional informative sources in order to increase your understanding of these, or any other, advanced functions. Subsequently, choose an advanced function and write down to educate regarding the meaning and application of it.
VLOOKUP & HLOOKUP for Dynamic Lookups
These two functions are easy to write, understand, and they’re a major time-saver for simpler lookups.
The VLOOKUP function can search for a specific category or value in a table and return the “opposing” value from adjacent columns.
HLOOKUP works exactly in the same way except that it looks horizontally rather than vertically.
“V” in VLOOKUP refers to vertical and “H” in HLOOKUP refers to horizontal.
If the data in your table has column headers, use the VLOOKUP function. If the data table has row headers and you need to search horizontally across the rows for a “match,” then use HLOOKUP.
The main limitation is that the value looked with VLOOKUP needs to be in the left-most table column and in HLOOKUP it has to be in the top row of the data set.
HLOOKUP is used to search along the top row and then look down to retrieve the result from a specified row – the second row in this instance.
The VLOOKUP formula syntax is the following
Limitations of VLOOKUP
One major limitation of VLOOKUP is that it cannot look to the left. The values to lookup must always be on the left-most column of the range and the values to return must be on the right hand side.
You cannot use the standard VLOOKUP to look at the columns and the rows to find an exact match. The INDEX and MATCH functions are used in this instance and are much more flexible than the classical VLOOKUP.
............sk