locked
Using a cell value for the table_array in a vlookup RRS feed

  • Question

  • I am attempting to make a vlookup function use a designated cell as the table_array value for the function. I have,

    =vlookup(a3, b2,2,FALSE)

    In cell B2 is the word poor, poor is a named range. When I change the formula to,

    =vlookup(a3,poor,2,false)

    it works. I have also tried to use vlookup(a3,indirect(b2),2,false) and vlookup(a3,indirect("b2"),2,false). The reason this is important is because the value in b2 will change depending upon the need. For instance it may contain the named range average, good, etc, I need what ever range name I type there to work in the vlookup function.

    • Moved by Liliane Teng Wednesday, October 26, 2011 8:11 AM (From:Excel for Developers)
    Thursday, October 13, 2011 6:05 PM

Answers

All replies

  • ~~>> vlookup(a3,indirect(b2),2,false)

    Well, it works for me of course with slightly different references.

    "Poor" in my sheet refers to A1:B6

    The text "Poor" is stored in cell F1

    Vlookup formula is in F3 which is

    =VLOOKUP(E3,INDIRECT(F1),2,0)

     


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Thursday, October 13, 2011 6:17 PM
  • The named range poor in my sheet is written as =OFFSET('Bonus Formulas'!$I$2,0,0,COUNT('Bonus Formulas'!$I:$J),2) When I name the range directly in the vlookup formula it works, but when I try and put the name in b2 and reference it, I get n/a or #ref depending on how I try and reference it.
    Thursday, October 13, 2011 6:25 PM
  • Can you upload a sample workbook? so that I can directly test it? That ways I wouldn't have to create a sample :)
    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Thursday, October 13, 2011 6:27 PM
  • Well I made an Example sheet but I am unsure how to link it to this location, so I am afraid before I get your help with the sheet I will need your help to get the sheet in here.

     

    Thursday, October 13, 2011 6:40 PM
  • >>>Well I made an Example sheet but I am unsure how to link it to this location, so I am afraid before I get your help with the sheet I will need your help to get the sheet in here.

    LOL no worries :)

    Either upload the file in <cite>https://skydrive.live.com/ </cite>OR www.wikisend.com and share the link here. If you plan to upload the file in skydrive then give it ample rights so that we can view it :)


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Thursday, October 13, 2011 6:43 PM
  • (Sigh) well looks like I wont be able to present an example sheet for now, I am on a work computer and they have blocked both of those links, so I will have to wait until I get home to upload the file.
    Thursday, October 13, 2011 6:54 PM
  • Here we go, link active https://skydrive.live.com/?cid=8e69aef53378777a#!/view.aspx?cid=8E69AEF53378777A&resid=8E69AEF53378777A!131
    Thursday, October 13, 2011 9:53 PM
  • Sorry to disappoint you but I guess You have not given it appropriate permissions. I cannot access it. Alternatively can you please upload it to www.wikisend.com
    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Friday, October 14, 2011 9:00 AM
  • I apologize for the inconvenience and I thank you for sticking with it, I have now edited the permissions to make it a public file.
    Friday, October 14, 2011 2:56 PM
  • Still trying to figure this problem out, so self bumping.

    Tuesday, October 18, 2011 4:37 PM
  • Hi Novafury,

    What do you want to get in "What I want" column? Could you please give a more detail explaination? By the way, for this problem is not related to programming issue, I recommend you could repost on Answers forum for more suggestions.

    http://answers.microsoft.com/en-us/default.aspx

    Have a nice day.

    Best regards

     


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 24, 2011 8:29 AM
  • The functional column shows what I have figured out can work with a named range and a formula. The what I want column contains the same formula except it is attempting to obtain the table array value from another cell, I want it to work that way, so that I can post a single formula into every cell, and simply change the name of the targeted cell to use a different named range. As for the answers forumn I am not sure where I would put that, if you could move this to the proper place as a moderator that would be helpful.

    Monday, October 24, 2011 4:21 PM
  • Hi Novafury,

    I am sorry I could not move this thread to Answers forum. You could open a new thread on Excel aspect of Answers forum. Please click "Ask a question" button. Please check:

    http://answers.microsoft.com/en-us/office/forum/excel

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 26, 2011 7:40 AM