VLOOKUP and HLOOKUP are very similar. For both, it will pull back the value you want in a range based on the value you are looking up. VLOOKUPs look like this:
=VLOOKUP(lookup_value, table_array, column_index_number, true/false)
Which translates to: LOOKUP(value to look up, where to look it up, when found - how many columns over it should move to pull the value, true = appox. match / false = exact match)
So, for example, let's say you are using Sheet2 to look up values in Sheet1. In Sheet2, column A has the same value as column A on Sheet1. Sheet2 column B is where you are entering the VLOOKUP. You want to pull a value in column C based on a value in column A on Sheet1. Cell B1 on Sheet2 formula will look like this:
=VLOOKUP(Sheet2!$A1, Sheet1!$A$2:$C$6, 3, FALSE)
On Sheet2, you are looking for value in cell A1. You want to look on Sheet1 from A2:C6 (the $ lock in the value when copying the formula down). 3 means you are moving to the 3rd column in the table (column C). False means you are looking for an exact match.
HLOOKUP is the same, but just using rows instead of columns to move and find the value. HLOOKUP formula looks like this:
=HLOOKUP(lookup_value, table_array, row_index_number, true/false)
Let's now say that cell A7 has the date 4/30/2012. On Sheet 1, cells B1:E1 have values 1/31/2012, 2/29/2012, 3/31/2012, 4/30/2012, respectively. You want to pull the 4th row down on Sheet1. Your formula will be:
=HLOOKUP($A$7, Sheet1!$A$1:$E$6, 4, FALSE)
You want to pull a value based on cell A7 which holds the value 4/30/2012. You want to look it up from A1:E6, and pull the 4th row down. So 4/30/2012 is in cell E1, this formula will pull the value in E4. False again means that it's looking for an exact value.
If you want a sample workbook to practice, email me at info@excelandvba.com
No comments:
Post a Comment