+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP through multiple sheets

  1. 05-16-2014, 02:00 PM #1
    fiveagainstone
    fiveagainstone is offline
    Registered User
    Join Date
    05-16-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2013
    Posts
    3

    VLOOKUP through multiple sheets

    I'm trying to create a VLOOKUP that will return a value in 'Tracking' from 1 of 4 different sheets. If the value isn't found in sheet 1, the lookup should continue to sheet 2. If not found in 2, move to 3 and so on. In cases where there is no value, I would like to return " ".

    Trying this:
    =IFERROR(VLOOKUP(A2,'1'!A:P,2,FALSE),IFERROR(VLOOKUP(A2,'2'!A:P,2,FALSE),IFERROR(VLOOKUP(A2,'3'!A:P,2,FALSE),IFERROR(VLOOKUP(A2,'4'!A:P,2,FALSE)," "))))

    This formula returns values only in sheet 1. It won't continue to 2 and so on. Also, this will return a 0 for a blank value. If working as intended, 'Tracking' will show "g" in B2 and " " in C2.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-16-2014, 02:50 PM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,786

    Re: VLOOKUP through multiple sheets

    try
    =IF(ISERROR(VLOOKUP(A2,'1'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'2'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'3'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'4'!A:P,2,FALSE))," ", VLOOKUP(A2,'1'!A:P,2,FALSE)),VLOOKUP(A2,'2'!A:P,2,FALSE)),VLOOKUP(A2,'3'!A:P,2,FALSE)),VLOOKUP(A2,'4'!A:P,2,FALSE))

    works for B2 - BUT the formula needs to change to give a different result for C2

    how do you want to change

    I assume
    =IF(ISERROR(VLOOKUP(A2,'1'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'2'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'3'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'4'!A:P,2,FALSE))," ", VLOOKUP(A2,'1'!A:P,2,FALSE)),VLOOKUP(A2,'2'!A:P,2,FALSE)),VLOOKUP(A2,'3'!A:P,2,FALSE)),VLOOKUP(A2,'4'!A:P,2,FALSE))

    becomes
    =IF(ISERROR(VLOOKUP(A2,'1'!A:P,3,FALSE)),IF(ISERROR(VLOOKUP(A2,'2'!A:P,3,FALSE)),IF(ISERROR(VLOOKUP(A2,'3'!A:P,3,FALSE)),IF(ISERROR(VLOOKUP(A2,'4'!A:P,3,FALSE))," ", VLOOKUP(A2,'1'!A:P,3,FALSE)),VLOOKUP(A2,'2'!A:P,3,FALSE)),VLOOKUP(A2,'3'!A:P,3,FALSE)),VLOOKUP(A2,'4'!A:P,3,FALSE))
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 05-16-2014, 03:02 PM #3
    fiveagainstone
    fiveagainstone is offline
    Registered User
    Join Date
    05-16-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: VLOOKUP through multiple sheets

    The VLOOKUP looks like it needs to be VLOOKUP(A2,'1'A:P,3,FALSE)), for C2 and VLOOKUP(A2,'1'A:P,4,FALSE)), for D2 and so on...

    Your formula appears to be working!! Awesome! The only other question I have is how to return " " instead of 0 for blanks.
    Reply With QuoteRegister To Reply
  4. 05-16-2014, 03:07 PM #4
    blueblink01
    blueblink01 is offline
    Registered User
    Join Date
    03-22-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2010
    Posts
    39

    Re: VLOOKUP through multiple sheets

    Hi fiveagainstone,
    Please try below formula

    =IF(ISNONTEXT(IF(ISERROR(VLOOKUP($A2,'1'!$A$1:$P$5,MATCH(C1,'1'!$A$1:$P$1,0),0)),"",IF(ISERROR(VLOOKUP(Tracking!$A2,'2'!$A$1:$P$5,MATCH(C1,'2'!$A$1:$P$1,0),0)),"",IF(ISERROR(VLOOKUP($A2,'3'!$A$1:$P$5,MATCH(C1,'3'!B1:Q1,0),0)),"",VLOOKUP(Tracking!$A2,'4'!$A$1:$P$5,MATCH(C1,'4'!$A$1:$P$1,0),0))))),"",IF(ISERROR(VLOOKUP($A2,'1'!$A$1:$P$5,MATCH(C1,'1'!$A$1:$P$1,0),0)),"",IF(ISERROR(VLOOKUP(Tracking!$A2,'2'!$A$1:$P$5,MATCH(C1,'2'!$A$1:$P$1,0),0)),"",IF(ISERROR(VLOOKUP($A2,'3'!$A$1:$P$5,MATCH(C1,'3'!B1:Q1,0),0)),"",VLOOKUP(Tracking!$A2,'4'!$A$1:$P$5,MATCH(C1,'4'!$A$1:$P$1,0),0)))))

    I also answered you inquiry to the post of etaf

    REgards,
    Last edited by blueblink01; 05-16-2014 at 03:11 PM.
    Reply With QuoteRegister To Reply
  5. 05-16-2014, 03:19 PM #5
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,786

    Re: VLOOKUP through multiple sheets

    ok so the error is if you cannot find the item you are looking up
    so some results may be a zero - correct ?

    i think the formula is not working correctly - due to using an iserror
    what you are testing for is if the result is "" or a zero , then you want to move onto the next vlookup - correct ?

    =IF(ISERROR(VLOOKUP(A2,'1'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'2'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'3'!A:P,2,FALSE)),IF(ISERROR(VLOOKUP(A2,'4'!A:P,2,FALSE))," ", VLOOKUP(A2,'1'!A:P,2,FALSE)),VLOOKUP(A2,'2'!A:P,2,FALSE)),VLOOKUP(A2,'3'!A:P,2,FALSE)),VLOOKUP(A2,'4'!A:P,2,FALSE))

    will become
    =IFERROR(IF(VLOOKUP(A2,'1'!A:P,2,FALSE)<>0,VLOOKUP(A2,'1'!A:P,2,FALSE),IF(VLOOKUP(A2,'2'!A:P,2,FALSE)<>0,VLOOKUP(A2,'2'!A:P,2,FALSE),IF(VLOOKUP(A2,'3'!A:P,2,FALSE)<>0,VLOOKUP(A2,'3'!A:P,2,FALSE),IF(VLOOKUP(A2,'4'!A:P,2,FALSE)<>0,VLOOKUP(A2,'4'!A:P,2,FALSE)," "))))," ")
    Reply With QuoteRegister To Reply
  6. 05-16-2014, 03:38 PM #6
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,318

    Re: VLOOKUP through multiple sheets

    Or take a look at this:
    http://www.ozgrid.com/VBA/VlookupAllSheets.htm
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  7. 05-16-2014, 03:45 PM #7
    fiveagainstone
    fiveagainstone is offline
    Registered User
    Join Date
    05-16-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: VLOOKUP through multiple sheets

    Thank you for all of the responses! Etaf, yes you are correct. If a value is 0 or blank, the formula should move to the next VLOOKUP.
    Heading home from work but once I'm in front of my Pc I'll send an update!
    Reply With QuoteRegister To Reply
  8. 05-16-2014, 04:30 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: VLOOKUP through multiple sheets

    This appears to work. Enter in Tracking!B2 and copy across and down:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by newdoverman; 05-16-2014 at 04:41 PM.
    <---------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
+ 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. Excel Vlookup, multiple sheets and multiple variables
    By caninekopz in forum Excel General
    Replies: 3
    Last Post: 11-27-2013, 10:33 AM
  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. Vlookup function to return multiple values from multiple sheets
    By bsamant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2013, 11:31 AM
  4. Macro using VLOOKUP with multiple matches between multiple sheets
    By Derrek0204 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2011, 06:20 PM
  5. VLookup Multiple Sheets - along single row across 52 sheets
    By Poolio19 in forum Excel General
    Replies: 8
    Last Post: 03-30-2011, 11:09 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1