+ Reply to Thread
Results 1 to 5 of 5

Vlookup across multiple sheets backwards compatible with older versions of Excel

  1. #1
    Forum Contributor
    Join Date
    12-01-2010
    Location
    Southampton, England
    MS-Off Ver
    Excel 2007
    Posts
    303

    Exclamation Vlookup across multiple sheets backwards compatible with older versions of Excel

    Hi Guys,

    I have a vlookup issue which is essentially twofold.

    1 - Performing a vlookup across multiple sheets
    2 - I want to be able to distribute this out, so the function used needs to be compatible with older versions of excel.

    If at all possible I don't want to have to use VBA in order to do this.

    As always it is easier to explain the problem with an example spreadsheet so I have attached an example spreadsheet to this question.

    If anyone could help I would be very grateful.

    Best regards

    Alan
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Vlookup across multiple sheets backwards compatible with older versions of Excel

    Is this what ur looking for
    paste the formula in C2 and drag down
    =INDEX(Sheet1!$A$2:$A$11,MATCH(Sheet2!B2,Names,0))
    Punnam

  3. #3
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Vlookup across multiple sheets backwards compatible with older versions of Excel

    Another way using Vlookup with help of Choose function.

    =IFERROR(VLOOKUP(B2,CHOOSE({1,2},Names,Sheet1!$A$2:$A$11),2,FALSE),"")

    Punnam

  4. #4
    Forum Contributor
    Join Date
    12-01-2010
    Location
    Southampton, England
    MS-Off Ver
    Excel 2007
    Posts
    303

    Re: Vlookup across multiple sheets backwards compatible with older versions of Excel

    Thanks Punnam, could you explain how this is working so I can apply it to more scenarios please.

    Quote Originally Posted by Punnam View Post
    Is this what ur looking for
    paste the formula in C2 and drag down
    =INDEX(Sheet1!$A$2:$A$11,MATCH(Sheet2!B2,Names,0))
    Punnam

  5. #5
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Vlookup across multiple sheets backwards compatible with older versions of Excel

    @ ad9051.

    =INDEX(Sheet1!$A$2:$A$11,MATCH(Sheet2!B2,Names,0))
    1) Index will return the specific data in Know "Range' based on it row number & Column Number
    for the instance Here the range is Sheet1!$A$2:$A$11 i have got the column i.e A ,
    2) Next is the row Number for this i have used a match Function
    Match will search a known value in know range and reflects the Respective Row number
    So from Step 1 i had freezed the column & step 2 had freezed row number which in term reflect the required cell address
    I am not got @ explanations
    Punnam

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel 2000 VBA Compatible with Newer Versions?
    By RockyMtnHi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2007, 07:42 AM
  2. [SOLVED] How compatible is Excel 2003 with previous versions
    By Rod in forum Excel General
    Replies: 1
    Last Post: 02-03-2006, 10:25 AM
  3. [SOLVED] Using Older versions of ms excel with 2003
    By BassJay in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2005, 02:05 PM
  4. Older versions of Excel
    By Jaygirl44 in forum Excel General
    Replies: 0
    Last Post: 05-13-2005, 06:21 PM
  5. how to write a code in VB6 compatible to all Excel Versions
    By rajesh_red1 in forum Excel General
    Replies: 0
    Last Post: 01-11-2005, 08:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1