+ Reply to Thread
Results 1 to 3 of 3

Vlookup + Address functions combined

  1. #1
    Peter
    Guest

    Vlookup + Address functions combined

    Dear Exellions,

    I am using the following formula to look for a date in current "Summary"
    sheet, then see if it can find relevent match in "ARBSCAPES" sheet and
    return a numerical value:

    =VLOOKUP(L$1,ARBSCAPES!$A$13:$I$126,9,FALSE)

    I need this formula to include something like the ADDRESS function to avoid
    having to type in an alternative sheet reference for the other 80 odd sheets
    in same Excel file.

    I, currently have the 80 odd sheets listed in column A of my "Summary"
    sheet, and would like the VLOOKUP to look something like this:

    =VLOOKUP(L$1,Address(A2)!$A$1:$B$126,2,FALSE), but this will not work.

    Eg, Summary sheet
    Row Col A Col B Col L1 Col M
    1 01/12/05
    01/11/05
    2 Beens
    =VLOOKUP(L$1,Address(A2)!$A$1:$B$12,2,FALSE)
    3 Shoots
    =VLOOKUP(L$1,Address(A3)!$A$1:$B$12,2,FALSE)
    4 Carrots
    =VLOOKUP(L$1,Address(A4)!$A$1:$B$12,2,FALSE)

    Eg, Beens sheet
    Row Col A Col B
    1 01/12/05 £5.25
    2 01/11/05 £7.50
    3 01/10/05 £2.25

    INDIRECT formula is not suitable in this scenario.

    Thanks.
    Peter



  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187
    try the indirect function

  3. #3
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187
    try
    =VLOOKUP(L$1, Indirect(A2 & "!$A$1:$B$12",true) ,2,FALSE)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1