+ Reply to Thread
Results 1 to 12 of 12

Using INDIRECT funtion for VLOOKUP

  1. 05-08-2014, 06:57 PM #1
    Spicey_888
    Spicey_888 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Question Using INDIRECT funtion for VLOOKUP

    Hi all,

    I am having issues using the INDIRECT function to lookup data from a sheet with the same name as that appearing in a given cell. For example, in cell D27 i have the text "S1_358_810" (Not including quotations). I also have a sheet named "S1_358_810". My formula is as follows;

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    However this is returning #N/A. There is a list of numbers in sheet S1_358_810 in column N and from that I want the value in column Q (thus 17).

    What am I doing wrong, is it something to do with the way I have used quotations in the formula?

    Kind Regards,
    Ben
    Reply With QuoteRegister To Reply
  2. 05-08-2014, 07:06 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,426

    Re: Using INDIRECT funtion for VLOOKUP

    Looks like you're trying to return column 17 from a single column range, "$N$4:$N$1000"


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 05-08-2014, 07:22 PM #3
    Spicey_888
    Spicey_888 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Question Re: Using INDIRECT funtion for VLOOKUP

    TMS,

    Can you explain what you mean by that. I am an excel amature.

    I have attached an example spread sheet to aid in working this problem out.

    \Example.xls

    Regards,
    Ben
    Reply With QuoteRegister To Reply
  4. 05-08-2014, 07:45 PM #4
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Using INDIRECT funtion for VLOOKUP

    I think that this is what you are looking for:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  5. 05-08-2014, 07:52 PM #5
    Spicey_888
    Spicey_888 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Re: Using INDIRECT funtion for VLOOKUP

    Hi Newdoverman,

    Yes that does what I want but as I said I wish to use the INDIRECT function or some equivelant as I am going to have potentially 100's of sheets with the sheet names listed in column D. Thus I can drag the formula down to auto fill for all sheet names and not have to re-enter each individual sheet name.

    Regards,
    Ben
    Reply With QuoteRegister To Reply
  6. 05-08-2014, 07:58 PM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,426

    Re: Using INDIRECT funtion for VLOOKUP

    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Regards, TMS
    Reply With QuoteRegister To Reply
  7. 05-08-2014, 07:59 PM #7
    Mazzaropi
    Mazzaropi is offline
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Re: Using INDIRECT funtion for VLOOKUP

    Spicey_888, Good evening.

    Try to use it:

    =VLOOKUP(F3,INDIRECT(D3&"!$N$4:$Q$91"),4,0)

    Is it what you want?
    I hope it helps.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marc?lio Lob?o
    Reply With QuoteRegister To Reply
  8. 05-08-2014, 08:22 PM #8
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Using INDIRECT funtion for VLOOKUP

    I have a hard time reading the " ' " combinations in formulae so this might be the same as TMS. If so, sorry. I have to use about 16 point font to see the details For the " ' " and " ' to work properly, spaces between can cause problems so don't include spaces in there...darned eyesight in the dark!


    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Just enlarged the responses....looks like you have workable solutions.
    Last edited by newdoverman; 05-08-2014 at 08:30 PM.
    Reply With QuoteRegister To Reply
  9. 05-08-2014, 08:47 PM #9
    Spicey_888
    Spicey_888 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Re: Using INDIRECT funtion for VLOOKUP

    Cheers newdoverman,

    Works ok in my example spread sheet but yet to implement it in the actual workbook I have created. Why is 4 the correct column index number when colum Q is the 17th column, thus i was originally using 17 with no avail?

    Regards,
    Ben
    Reply With QuoteRegister To Reply
  10. 05-08-2014, 10:08 PM #10
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,426

    Re: Using INDIRECT funtion for VLOOKUP

    It's relative to the range you specify. VLOOKUP always searches column 1 of the specified range so, in this case, column N. Column Q is column 4 relative to column N.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  11. 05-08-2014, 10:21 PM #11
    Spicey_888
    Spicey_888 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Re: Using INDIRECT funtion for VLOOKUP

    Ok now I am satisfied.

    FYI; I have already thanked the people that have helped and I know how to change to SOLVED which i will now do as my question has been answered
    Reply With QuoteRegister To Reply
  12. 05-08-2014, 10:44 PM #12
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,426

    Re: Using INDIRECT funtion for VLOOKUP

    FYI; I have already thanked the people that have helped
    So, I didn't help at all in posts #2, #6 and #10?

    I'll remember that


    and I know how to change to SOLVED
    It's a standard message ... and not everyone does.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. V Lookup & Indirect Funtion!!
    By gvasudeva in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-20-2013, 06:12 PM
  2. indirect funtion
    By dannymc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-12-2007, 01:25 PM
  3. [SOLVED] indirect funtion
    By Pete Elbert in forum Excel General
    Replies: 1
    Last Post: 04-27-2006, 10:50 PM
  4. [SOLVED] Vlookup (and/or) IF funtion
    By Serge in forum Excel General
    Replies: 1
    Last Post: 02-20-2006, 01:45 AM
  5. [SOLVED] Trying to use the INDIRECT funtion with a relative Row reference
    By Paul Cahoon in forum Excel General
    Replies: 1
    Last Post: 12-29-2005, 04:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1