# VLOOKUP formula and understanding the function

Patricia Timm used Ask the Experts™
on
I have the following formula =VLOOKUP(\$A\$6,'Functions and Headcount'!\$A1:\$AW38,7,FALSE)
I am in a worksheet called A
I understand looks for value in column A6 (A6 is based on a dropdown choice from A5) * I am guessing that it looks for this value in worksheet A
Next it searches for a matching value in the range A1:AW38 in worksheet called Functions and Headcount
then if it finds a match it places the value in what column and which worksheet A or Functions and Headcount?

Also what does the ! mean in the above VLOOKUP?
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
This is a separator between the sheetname and the range address.
Consultant
Commented:
VLOOKUP means vertical lookup.  There is a HLOOKUP that looks up horizonal.  Your formula,

looks up the value shown in A6 in the the  array A1:AW38, matching the first column A1, and when finding a match (must be an exact match because you used "FALSE", brings back the value in the 7th column.  That is what the 7 stands for.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If your VLOOKUP formula is placed in worksheet A, it will take the value in cell A6 in that worksheet and look for it in worksheet Functions and Headcount cells A1:A38. If it finds it, the formula will return a value from the same row in column G. If VLOOKUP does not find a match in column A, it will return the #N/A error value to let you know it failed. The value is returned to the cell on worksheet A containing the VLOOKUP formula.
Consultant

Commented:
The value is returned in the cell where the formula resides.  If you are in worksheet A, then there must be another worksheet in the workbook called Functions and Headcount'!.
Consultant

Commented:
Yea, byundt said it better...
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Amplifying what Syed said, the exclamation mark ! in the formula is used to separate the worksheet name from the cell address. Excel looks for the exclamation mark when it parses the formula to figure out where to go for your data.

Furthermore, because there are spaces in the worksheet name Functions and Headcount, Excel requires you to surround the worksheet name with single quotes. Those single quotes would be optional if you renamed the worksheet without spaces. For example, both of these formulas are valid:
``````=VLOOKUP(\$A\$6,FunctionsAndHeadcount!\$A1:\$AW38,7,FALSE)