VLOOKUP formula and understanding the function

Patricia Timm
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?
Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is a separator between the sheetname and the range address.
Tom FarrarConsultant
Commented:
VLOOKUP means vertical lookup.  There is a HLOOKUP that looks up horizonal.  Your formula,

VLOOKUP($A$6,'Functions and Headcount'!$A1:$AW38,7,FALSE)

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.
byundtMechanical 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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Tom FarrarConsultant

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'!.
Tom FarrarConsultant

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)
=VLOOKUP($A$6,'FunctionsAndHeadcount'!$A1:$AW38,7,FALSE)

Open in new window


Amplifying Tom Farrar's point about the 7 meaning column G, your lookup table is in worksheet Functions and Headcount columns A:W. The VLOOKUP formula is being asked to return a value from the 7th column in that lookup table. That's column G.

If you inserted a bunch of columns to the left of column A on that worksheet, VLOOKUP would still be returning a value from the 7th column of the lookup table in its new location. For example, if you inserted five columns, the lookup table would move to columns F:AB. The 7th column of that lookup table would now be column L. The VLOOKUP formula using the new lookup table location returns the same answer as it did the original lookup table location.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial