Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Name LOOKUP range 1
- Thread starter ZoomerZ
- Start date
bluedragon2
IS-IT--Management
A range of cells has been named emp. You can designate a name to a range/array of cells. So, if cells A1 thru C50 was named emp, then the lookup command will look in those cells for the information.
[Blue]Blue[/Blue] [Dragon]
If I wasn't Blue, I would just be a Dragon...
[Blue]Blue[/Blue] [Dragon]
If I wasn't Blue, I would just be a Dragon...
It means that someone has already named a range (called "emp"), and this is where your lookup formula is looking.
To see what the range is, you can either choose "emp" from the name box (just to the left of the formula bar)...
... or use the menu option "Insert - Name - Define" to see a list of the ranges in your workbook.
If you don't know how the lookup function is working, just post back. But I think it will help you to see what the range is.
To see what the range is, you can either choose "emp" from the name box (just to the left of the formula bar)...
... or use the menu option "Insert - Name - Define" to see a list of the ranges in your workbook.
If you don't know how the lookup function is working, just post back. But I think it will help you to see what the range is.
AnotherHiggins
Technical User
Select the lookup range (area where you want to search for value of B2), then go to Insert > Name > Define
Type in a name (emp) and press "Add".
Now the name emp is associated with that range!
[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886
Type in a name (emp) and press "Add".
Now the name emp is associated with that range!
[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886
- Thread starter
- #5
I've tortured you guys before with the same thing then I thought it'll go away by itself lol
Noops, it came back and darn formula does not work whatever I do. I am always stuck with simple stuff!
So there is a second Worksheet emp and column A name emp.
I am inserting formula on a first WSheet. Data was copy/pasted from Access table and query to both, so it is General now.
Now it gives me #REF
Thanks to all
P.S. Yeah, I performed all the steps you've suggested
Noops, it came back and darn formula does not work whatever I do. I am always stuck with simple stuff!
So there is a second Worksheet emp and column A name emp.
I am inserting formula on a first WSheet. Data was copy/pasted from Access table and query to both, so it is General now.
Now it gives me #REF
Thanks to all
P.S. Yeah, I performed all the steps you've suggested
#REF generally means there is something wrong with the range it is trying to look in (usually it means the cell(s) originally referenced in the formula have been deleted)
Do you see #REF in the formula itself (e.g., LOOKUP(B2,#REF,2,false), or just in the result?
If you know the range you want to look up in, I would try just renaming the range (use Insert - Name - Define), and then reset your formula to LOOKUP(B2,newname,2,false)
If you want to send me the sheet, I'll take a look at it. Usually once you have a functioning lookup (or vlookup or hlookup) once, it's easier to understand how it's working.
Do you see #REF in the formula itself (e.g., LOOKUP(B2,#REF,2,false), or just in the result?
If you know the range you want to look up in, I would try just renaming the range (use Insert - Name - Define), and then reset your formula to LOOKUP(B2,newname,2,false)
If you want to send me the sheet, I'll take a look at it. Usually once you have a functioning lookup (or vlookup or hlookup) once, it's easier to understand how it's working.
- Thread starter
- #7
This will give you a sample of how the range should work.
Click 'INSERT' select 'NAME' choose 'DEFINE'
Click on 'INFO' and it will show you the range selected for that data name.
Jim
Click 'INSERT' select 'NAME' choose 'DEFINE'
Click on 'INFO' and it will show you the range selected for that data name.
Jim
-
1
- #9
What is your formula and what is tha range of cells named "emp" ie is it A1:A100, A1:C10000 etc etc ??? This should be a pretty easy one to solve
pbbriggs - please do not offer to take threads off-line except for exceptional circumstances. This is not a helpdesk site and as such, everyone should have access to ALL the answers given here - which you don't get as soon as a thread is taken off-line
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
pbbriggs - please do not offer to take threads off-line except for exceptional circumstances. This is not a helpdesk site and as such, everyone should have access to ALL the answers given here - which you don't get as soon as a thread is taken off-line
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
Have you taken a look at the VLOOKUP FAQ I have in the FAQ section?
How does VLOOKUP work??
faq68-4743
Using your formula as quoted
=VLOOKUP(B2,emp,2,FALSE)
which just for the record can also be written
=VLOOKUP(B2,emp,2,0)
What that formula is going to try to do is to take a value that you specify (in this case whatever is in B2), and then go and look it up in a table (In this case a table named 'emp').
It will look for that value in the leftmost column of the table, and either find it or the closest match (In this case an exact match because you specified that by using FALSE/0 as your optional 4th argument), and will then return the corresponding value on the same row, in whatever column of that table that you tell it to (In this case the second column, which you specified by using 2 as your second argument):-
Example - With say the number 10 in B2, and the following table named 'emp', and by that I mean you have selcted the range D1:E5 and done Insert / Name / Define / name it 'emp' but without the quotes obviously.
D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40
So, with the formula being =VLOOKUP(B2,emp,2,0) it will first take the value in B2 which is 10, then go look for it in the leftmost column (D) of your specified table 'emp', and it will then try to find that number. It will find the 10, and the 2 in the formula says to go and get the value in the 2nd column (E) in your table, that is on the same row as the 10. That value in this case is
0.25
If you put the value 20 or 30 etc into B2 now, you will see the result of the formula change, because now it will either find those numbers and will subsequently return the corresponding values (or an error if the value does not exist).
Regards
Ken.................
----------------------------------------------------------------------------
![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
It's easier to beg forgiveness than ask permission![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
----------------------------------------------------------------------------
How does VLOOKUP work??
faq68-4743
Using your formula as quoted
=VLOOKUP(B2,emp,2,FALSE)
which just for the record can also be written
=VLOOKUP(B2,emp,2,0)
What that formula is going to try to do is to take a value that you specify (in this case whatever is in B2), and then go and look it up in a table (In this case a table named 'emp').
It will look for that value in the leftmost column of the table, and either find it or the closest match (In this case an exact match because you specified that by using FALSE/0 as your optional 4th argument), and will then return the corresponding value on the same row, in whatever column of that table that you tell it to (In this case the second column, which you specified by using 2 as your second argument):-
Example - With say the number 10 in B2, and the following table named 'emp', and by that I mean you have selcted the range D1:E5 and done Insert / Name / Define / name it 'emp' but without the quotes obviously.
D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40
So, with the formula being =VLOOKUP(B2,emp,2,0) it will first take the value in B2 which is 10, then go look for it in the leftmost column (D) of your specified table 'emp', and it will then try to find that number. It will find the 10, and the 2 in the formula says to go and get the value in the 2nd column (E) in your table, that is on the same row as the 10. That value in this case is
0.25
If you put the value 20 or 30 etc into B2 now, you will see the result of the formula change, because now it will either find those numbers and will subsequently return the corresponding values (or an error if the value does not exist).
Regards
Ken.................
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission----------------------------------------------------------------------------
Grrrrr - Slight correction
(In this case the second column, which you specified by using 2 as your second argument):-
should have been
(In this case the second column, which you specified by using 2 as your THIRD argument):-
Regards
Ken...........
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------
(In this case the second column, which you specified by using 2 as your second argument):-
should have been
(In this case the second column, which you specified by using 2 as your THIRD argument):-
Regards
Ken...........
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission----------------------------------------------------------------------------
- Thread starter
- #12
WSheet1
pay date EENO Formula
5/21/2004 101565 =VLOOKUP(B2,emp,2,false)
5/21/2004 240028
5/21/2004 102061
5/21/2004 220053
WSheet emp
emp Count
101565 3
102061 1
150011 1
160005 1
160008 1
That formula should give me 3, doesn't it?
It is too easy to even talk about it... but it does give me #REF. I love Excel
Thanks
pay date EENO Formula
5/21/2004 101565 =VLOOKUP(B2,emp,2,false)
5/21/2004 240028
5/21/2004 102061
5/21/2004 220053
WSheet emp
emp Count
101565 3
102061 1
150011 1
160005 1
160008 1
That formula should give me 3, doesn't it?
It is too easy to even talk about it... but it does give me #REF. I love Excel
Thanks
WHAT IS EMP ???????????
What range does it cover ????
I am certain that your error is caused because your NAMED RANGE "emp" being only 1 column wide OR that you don't actually have a named range called EMP.
Your formula:
=VLOOKUP(B2,emp,2,false)
will work ONLY if ALL the following are true
1: You have a NAMED RANGE called EMP which covers AT LEAST 2 COLUMNS worth of data - doesn't matter how many rows
2: The value in B2 exists in that named range in the 1st COLUMN
3: Both B2 and the entry in the named range are the same data type (this is an additional as it would not cause the #REF! error)
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
What range does it cover ????
I am certain that your error is caused because your NAMED RANGE "emp" being only 1 column wide OR that you don't actually have a named range called EMP.
Your formula:
=VLOOKUP(B2,emp,2,false)
will work ONLY if ALL the following are true
1: You have a NAMED RANGE called EMP which covers AT LEAST 2 COLUMNS worth of data - doesn't matter how many rows
2: The value in B2 exists in that named range in the 1st COLUMN
3: Both B2 and the entry in the named range are the same data type (this is an additional as it would not cause the #REF! error)
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
- Thread starter
- #14
All in my previous post is copied from actual Excel Spreadsheet.
WSheet1
---------------------------------------------------
Column A Column B Column C
pay date EENO Formula
5/21/2004 101565 =VLOOKUP(B2,emp,2,false)
5/21/2004 102061
WSheet emp
Column A Column B
emp Count
101565 3
102061 1
All are General as I said copy/pasted from Access table.
Rows are 1,2,3 (it is beginning of a doc)
What I need to say is :
Lookup value in emp where EENO = emp
which suppose to give me 3 and 1 in WSheet1 ColumnC.
Thanks
WSheet1
---------------------------------------------------
Column A Column B Column C
pay date EENO Formula
5/21/2004 101565 =VLOOKUP(B2,emp,2,false)
5/21/2004 102061
WSheet emp
Column A Column B
emp Count
101565 3
102061 1
All are General as I said copy/pasted from Access table.
Rows are 1,2,3 (it is beginning of a doc)
What I need to say is :
Lookup value in emp where EENO = emp
which suppose to give me 3 and 1 in WSheet1 ColumnC.
Thanks
- Thread starter
- #15
You are still not answering my questions !!
1: Do you have a named range called emp ??? YES or NO
2: If above is YES - what range of cells does it cover ???
Please reply with something like A1:A100 or A1:B100 like you would see in a formula
If you do not have a named range called emp then your formula cannot work as it is - you would need to substitute a range reference for "emp"
does this work ??
=vlookup(B2,emp!$A$1:$B$10,2,false)
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
1: Do you have a named range called emp ??? YES or NO
2: If above is YES - what range of cells does it cover ???
Please reply with something like A1:A100 or A1:B100 like you would see in a formula
If you do not have a named range called emp then your formula cannot work as it is - you would need to substitute a range reference for "emp"
does this work ??
=vlookup(B2,emp!$A$1:$B$10,2,false)
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
- Thread starter
- #17
Because the 2nd and 3rd arguments of the VLOOKUP formula must be related.
The 3rd argument is key here ie (B2,emp,2,false)
If the range "emp" only has 1 column, then you cannot retrieve data from its 2nd column as it does not have one. The 3rd argument tells the formula to "move across a certain number of columns WITHIN the range specified in the 2nd argument". If there are not enough columns in the range, you get the #REF! error. Therefore, if you want to use a named range, it must:
1: include the data to be matched in the far left column OF THE RANGE
2: Have at least as many columns as is specified in the 3rd argument
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
The 3rd argument is key here ie (B2,emp,2,false)
If the range "emp" only has 1 column, then you cannot retrieve data from its 2nd column as it does not have one. The 3rd argument tells the formula to "move across a certain number of columns WITHIN the range specified in the 2nd argument". If there are not enough columns in the range, you get the #REF! error. Therefore, if you want to use a named range, it must:
1: include the data to be matched in the far left column OF THE RANGE
2: Have at least as many columns as is specified in the 3rd argument
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
- Thread starter
- #19
OK, and if one dealing with this once a year - how one can find this all out? I know experience is everything but I am not dealing with Excel enough to gain one. So is there books you would recommend?
Thanks so much for sticking up with me, there is not enough stars
Thanks so much for sticking up with me, there is not enough stars
It would've been better to use the function help and just use dragging the mouse pointer to define the range to be used
Best bet, if you don't know exactly how to use a formula, is to enter the formula name and 1st bracket into a blank cell - in this instance, that would mean typing
=VLOOKUP(
Once you have done that, click on the fx (insert function) button next to the formula bar. Yuo should get a little dialog box which seperates each argument and gives a short description of what it is and its purpose in the formula - should usually be enough to get you going
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
Best bet, if you don't know exactly how to use a formula, is to enter the formula name and 1st bracket into a blank cell - in this instance, that would mean typing
=VLOOKUP(
Once you have done that, click on the fx (insert function) button next to the formula bar. Yuo should get a little dialog box which seperates each argument and gives a short description of what it is and its purpose in the formula - should usually be enough to get you going
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
- Status