I'm using the VLOOKUP function to find a specific value in a table and enter its corresponding value into a cell. The formula is below:
=VLOOKUP(Q2,HBF!A1:B72,2,FALSE)
This formula works fine for the first cell, but when I copy it into cells below it in my table, the cell references change. What I want the function to do is change the lookup_value (Q2) but leave the table array (HBF!A1:B72) the same. So I want the formula to look something like this:
=VLOOKUP(Q3,HBF!A1:B72,2,FALSE)
=VLOOKUP(Q4,HBF!A1:B72,2,FALSE)
=VLOOKUP(Q5,HBF!A1:B72,2,FALSE)
=VLOOKUP(Q6,HBF!A1:B72,2,FALSE)
Instead, I'm getting something like this:
=VLOOKUP(Q3,HBF!A2:B73,2,FALSE)
=VLOOKUP(Q4,HBF!A3:B74,2,FALSE)
=VLOOKUP(Q5,HBF!A4:B75,2,FALSE)
=VLOOKUP(Q6,HBF!A5:B76,2,FALSE)
Can anyone suggest how I might change the "lookup_value" depending on the cell, but keep the "table_array" field the same?
Thanks
-zack
see: http://office.microsoft.com/en-us/ex...005198323.aspx specifically re: absolute & relative referencing.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
=VLOOKUP(Q3,HBF!$A$1:$B$72,2,FALSE)
Look up Excel help on absolute and relative references....
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks