Hello,
I am trying to use a VLOOKUP formula, and I cannot understand why it is not working correctly. Here is the function, which is pretty simple:
Does this have something to do with the fact that I'm trying to use an e-mail address as the search key? I've tried removing hyperlinks and formatting the columns as text, but it doesn't seem to help.
Please Login or Register to view this content.
I have attached a sample workbook -- I appreciate any help anyone can offer!
Hello this error occurs because of the wrong reference
In the array you have K to L column but in column number you are having 3....i.e. you must have at least three column i.e. array must be at least from column K to M
Happy to Help
How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html
"I don't get things easily, so please be precise and elaborate"
If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.
Sourabh
Also vllokup cant be used that way
You can use a index match function like this
B3 =INDEX(DATA!$A:$S,MATCH(PEOPLE!$A1,DATA!$K:$K,0),8)
for extracting the last name
This solves my problem - thank you!
Out of curiosity, lets say the key is in column K, and the data I want to return is in H. Is there a way to do that?
In other words, is there some way to basically do something like this:
=VLOOKUP(PEOPLE!A1, DATA!K1:L9999, -3, 0)
...trying to get a value that is three cells back from the key cell? Obviously this won't work, based on what you two have described I'm doing wrong, but is there some other way to do this with a VLOOKUP?
Would I have to move the column over to the right of the key column?
Thanks so much for your help folks!
[B]No, you cannot use negative values in vlookup, it cannot (generally) look "behind" itself, you would change to INDEXMATCH for thisOut of curiosity, lets say the key is in column K, and the data I want to return is in H. Is there a way to do that?
In other words, is there some way to basically do something like this:
=VLOOKUP(PEOPLE!A1, DATA!K1:L9999, -3, 0)
The sample provided in post #3...
B3 =INDEX(DATA!$A:$S,MATCH(PEOPLE!$A1,DATA!$K:$K,0),8)
Instead of 8 (bolded), you use anothetr match to find the column number
B3 =INDEX(DATA!$A:$S,MATCH(PEOPLE!$A1,DATA!$K:$K,0),MATCH(REPORT!B$2,DATA!$A$1:$S$1,0)
Assuming Data had headings in row 1
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi,
I had a look at the attached - you only have 2 columns in your "SearchTable" reference giving you a vlookup #Ref! Error. Check out the following article for possible reasons leading to this (for example, did you delete a column?)
http://www.excelvlookuphelp.com/my-v...returning-ref/
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks