LinkBack URL
About LinkBacks
Okay so I need some help to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.
HTML Code:
Okay so I need some help to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.
HTML Code:
Last edited by JustMe602; 06-01-2007 at 10:16 AM.
How about:
=VLOOKUP(B2,SHEET2!A1:A100,2,FALSE)
Let me know if that works
Due to the values needing to be in ascending order I do believe that is the reason that the Vlookup function will not work in the scenero. Both Sheet 1 & Sheet 2 are not in ascending order based off the information in Column B in Sheet 1 and Column A in Sheet 2.
Last edited by JustMe602; 06-01-2007 at 10:18 AM.
It is possible that your lookup range and lookup array are not the same format. If you post a sample of your workbook, it will be easier to specify the problem.
The data in sheet1 column B (all commencing with "9") does not match any of the data in sheet2 column A ( all commencing with "5"). I assume these are phone numbers, so to find a match you need to eliminate the area codes from the formula.Originally Posted by JustMe602
Trish in Oz
-------------
A problem well defined is a puzzle half solved
If you attach a sample of your workbook it will be easier to find a solution.Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
http://www.excelforum.com/faq.php?fa...b3_attachments
In the example of data that I initially supplied the data in sheet2 did not have any values that matched sheet1. I upated the example but still need help with the formula. I don't want to get rid of the the first 3 digits.
The data in both sheets have the same formating. So that shouldn't be the problem.
I almost have it in the initial formula, except it only returns column 2 in sheet2 when the information matches the first record in the sheet2.
Thanks the input of information thus far from everyone.
VLOOKUP is definitely the function that you need. It's just a matter of applying it.
What exactly is it doing incorrectly? Are your range set correctly? Did you absolute (use the $ sign) the necessary ranges? (It should look like: =VLOOKUP(B2,SHEET2!$A$1:$A$100,2,FALSE). )
Other than that, I would again recommend posting a sample of your spreadsheet (use dummy data if your info is confidential).
I get the #ref! error when I use the Vlookup function as outlined in the previous post. I have attached an example of the data.
I see the error, and I initially advised you incorrectly.
Your lookup range has to encompass all of the data you are looking in. You have:
=VLOOKUP(B2,Sheet2!$A$1:$A$2000,2,FALSE)
It should be
=VLOOKUP(B2,Sheet2!$A$1:$B$2000,2,FALSE)
Notice the lookup range has changed from A1:A1000 to A1:B1000
Try VLOOKUP again but with this small modification
=VLOOKUP(B2,SHEET2!$A$1:$B$100,2,FALSE).
The previous example was trying to look in the second column of a single column array!
Hence the #REF error.
Mark.
BigBas Beat me to it!:
My typing is too slow.
rolleyes:
I now understand the error and indeed the vlookup function is working perfectly. Thanks for all the information.
JustMe602
Your typing isn't too slow. You just chose to use the red font to display the error, which gave me the slight advantage.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks