Hi,
I humbly seek help and tips to get my Index Function fine tuned as it does not return correctly. I have two sheets, Sheet1 consists of Company Name and Person In charge. Sheet2 is a full list detailing specific person in charge in a specific company. I need sheet1 Column B to be updated automatically when Column A is entered. Attached is the file for your easy understanding. Any help is greatly appreciated.
is there a specific reason sheet2 is laid out the way it is? it would be far easier to find what you need if it was laid out more along the lines of what you have on sheet1?
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,
The file was originally created by my friend and I am asked to update it in Sheet1. Is vlookup and index function can accomplish the task? Thanks in advance.
As FDibbins said, i think that can not get the results as your data in sheet 2, are in this laid out.Originally Posted by FDibbins
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Besides Index and Vlookup, is there any other function that can get my results?
My opinion, is NO.
You have to change the way that your data exist in your second sheet.
Hi Fotis1991,
Thanks for your confirmation.
Obviously there's a limit to 7 of these for Excel 2003, but this would work:
B2:
=IF(ISNUMBER(MATCH(A2, Sheet2!$2:$2, 0)), HLOOKUP(A2, Sheet2!$2:$3, 2, 0),
IF(ISNUMBER(MATCH(A2, Sheet2!$5:$5, 0)), HLOOKUP(A2, Sheet2!$5:$6, 2, 0),
IF(ISNUMBER(MATCH(A2, Sheet2!$8:$8, 0)), HLOOKUP(A2, Sheet2!$8:$9, 2, 0))))
Since the workbook has macros in it already, we could create a custom function to do this, too. More plumbing...
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi JBeaucaire,
I will give it a try,thanks a lot.
If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.
Hi JBeaucaire,
If my company name in Sheet2 is very long, will the function work? Thank you.
Why wouldn't it? What happened when you tried it?
It is still workable until 5 of these in B2
=IF(ISNUMBER(MATCH(A21, Sheet2!$2:$2, 0)), HLOOKUP(A21, Sheet2!$2:$3, 2, 0),
IF(ISNUMBER(MATCH(A21, Sheet2!$5:$5, 0)), HLOOKUP(A21, Sheet2!$5:$6, 2, 0),
IF(ISNUMBER(MATCH(A21, Sheet2!$8:$8, 0)), HLOOKUP(A21, Sheet2!$8:$9, 2, 0),
IF(ISNUMBER(MATCH(A21, Sheet2!$11:$11, 0)), HLOOKUP(A21, Sheet2!$11:$12, 2, 0),
IF(ISNUMBER(MATCH(A21, Sheet2!$14:$14, 0)), HLOOKUP(A21, Sheet2!$14:$15, 2, 0))))))
can it go more?
Last edited by seanyeap; 10-22-2012 at 10:13 AM.
Yes, looks like you can do two more.
After 2 more, it will be the end? I have huge rows in the sheet, can I ask for a Custom Function? Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks