+ Reply to Thread
Results 1 to 7 of 7

Vlookup or Index Match to check value across multiple sheets

  1. #1
    Registered User
    Join Date
    02-16-2015
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    39

    Vlookup or Index Match to check value across multiple sheets

    Hi..
    I am working on a file which has got 5 spreadsheets viz Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet 5.. Every sheet has got a column called Tag which consists of a integer value. Sheet 4 consists of tag and owner. Now I want to populate the value in sheet 5 only if the tag in Sheet 4 matches with the tag in any of the sheets(sheet 1, sheet 2,sheet 3). If the tag is not available in any of the sheet. It should return Blank.

    I applied And with Index match but it is giving me #Value Error.

    Please help.

    Thanks in advance for the help
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Turkey
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Vlookup or Index Match to check value across multiple sheets

    I attached a sample sheet with UDF(User Defined Function)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-16-2015
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    39

    Re: Vlookup or Index Match to check value across multiple sheets

    Quote Originally Posted by Ali Kırksekiz View Post
    I attached a sample sheet with UDF(User Defined Function)
    Hi Ali..
    I appreciate your reply..but I am still getting #Name errorAli.JPG

    when I opened the workbook, it prompted that there is circular reference.. I am not sure whether I have made my question clear or not. I have got (tags & owners) in sheet 4. and only tags in (sheet 1, sheet 2, sheet 3)... Sheet 5 and sheet 4 are of the same format.. I want that sheet 5 should populate the owner name if the tag is present in (sheet 4) & in any of the sheets (sheet 1, sheet 2, sheet 3). If the value in sheet 4 could not match in (sheet 1 or sheet 2 or sheet 3) then the Owner in Sheet 5 should return Blank.

    What i could get from your UDF was that you have taken every worksheet in consideration to match

    I hope i am not confusing you..Please let me know if you have any doubt.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Vlookup or Index Match to check value across multiple sheets

    Hi. Here's a non-UDF approach. A longish formula...
    Attached Files Attached Files
    Glenn



  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Vlookup or Index Match to check value across multiple sheets

    Hi there. Thanks for the feedback.

  6. #6
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Turkey
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Vlookup or Index Match to check value across multiple sheets

    Alternative formula

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,247

    Re: Vlookup or Index Match to check value across multiple sheets

    Try this,
    Please Login or Register  to view this content.
    ARRAY formula is used

    To enter ARRAY formula
    Paste the formula
    Press F2
    Press Ctrl+Shift+Enter keys together.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 04-03-2015 at 04:55 AM.

+ 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. [SOLVED] Vlookup/Index/Match on multiple sheets data
    By yabi0823 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-30-2014, 03:47 PM
  2. Vlookup (or index/match) with multiple criteria over multiple sheets
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2013, 01:56 PM
  3. Looking up data from two sheets with vlookup or index and match
    By joeycrak in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2013, 04:56 PM
  4. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  5. Index / Match / Vlookup Loop across 3 sheets
    By NewExcelUser in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2009, 03:27 AM

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