Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Lookup Value Across Multiple Worksheets

  1. #1
    Join Date
    24th April 2008
    Posts
    4

    Lookup Value Across Multiple Worksheets

    Hi,

    Would like to lookup values across multiple worksheets. I am able to do this for a single worksheet using OFFSET(Karthik!$E$1,MATCH(A2,Karthik!E2:E21,0),-3).My Work Sheet Names are Karthik & Ramesh. How can i reference these worksheets in the above formula?

    Pls help.

    Thanks

    Socratophile

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2008
    Location
    Work in North Wales
    Posts
    262

    Re: Lookup Value In Multiple Worksheets

    Sorry, I don't understand what you mean by looking up across multiple worksheets.

    Do you want to look in one sheet and, if the lookup can't be found, look in the next worksheet?

    Or do you want to lookup the same value in two sheets and combine the results somehow?

    Added by admin
    Vlookup Across Excel Worksheets
    Last edited by Dave Hawley; April 24th, 2008 at 18:18.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th April 2008
    Posts
    4

    Re: Lookup Value In Multiple Worksheets

    Thanks for the reply.

    The value that I am looking up appears only in one of the sheets. So if the lookup can't be found in one sheet, look in the next worksheet.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Lookup Value In Multiple Worksheets

    Hi,

    If the return value is numeric then try

    =LOOKUP(9.99999E+307,CHOOSE({1,2,3},0,INDEX(Ramesh!B2:B21,MATCH(A2,Ramesh!E2:E21,0)),INDEX(Karthik!B2:B21,MATCH(A2,Karthik!E2:E21,0))))

    if it's text then

    =LOOKUP("zzzzzzzzzzz",CHOOSE({1,2,3},"Not found",INDEX(Ramesh!B2:B21,MATCH(A2,Ramesh!E2:E21,0)),INDEX(Karthik!B2:B21,MATCH(A2,Karthik!E2:E21,0))))

    HTH
    Auto Merged Post Until 24 Hrs Passes;

    If it's both then


    in a spare cell (e.g C2),

    =LOOKUP("zzzzzzzzzzz",CHOOSE({1,2,3},"Not found",INDEX(Ramesh!B2:B21,MATCH(A2,Ramesh!E2:E21,0)),INDEX(Karthik!B2:B21,MATCH(A2,Karthik!E2:E21,0))))

    And try

    =IF(C2="Not found",LOOKUP(9.99999E+307,CHOOSE({1,2,3},0,INDEX(Ramesh!B2:B21,MATCH(A2,Ramesh!E2:E21,0)),INDEX(Karthik!B2:B21,MATCH(A2,Karthik!E2:E21,0)))),C2)

    You could hide column C or change the font color of C2.
    Last edited by Krishnakumar; April 24th, 2008 at 17:34. Reason: Auto Merged Doublepost

  5. #5
    Join Date
    24th April 2008
    Posts
    4

    Re: Lookup Value In Multiple Worksheets

    Thanks for the reply Kris. The formula did not work. Let me explain. I have a bunch of values listed in a worksheet under column A. These values are also present in either sheets "Karthik" or "Ramesh" (part of the same workbook) and not both.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Lookup Value In Multiple Worksheets

    Quote Originally Posted by socratophile
    Thanks for the reply Kris. The formula did not work. Let me explain. I have a bunch of values listed in a worksheet under column A. These values are also present in either sheets "Karthik" or "Ramesh" (part of the same workbook) and not both.
    That's exactly what my formula does. If it doesn't work for you, attach the workbook here.

  8. #8
    Join Date
    24th April 2008
    Posts
    4

    Re: Lookup Value Across Multiple Worksheets

    Hi Kris,

    Your formula worked! Guess i just had to understand the logic and map the correct values. Thank you so much for this.

    Have a nice day!

    Regards,

    Soc
    Auto Merged Post Until 24 Hrs Passes;

    Kris,

    When i use the formula for a numeric value I get a "0" for references where there is no value. Is there anyway i can get the formula to diaplay a blank in such casesand not a 0 ?

    Regards,

    Soc
    Last edited by socratophile; April 29th, 2008 at 12:52. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Lookup Value Across Multiple Worksheets

    Hi,

    Custom format the cell

    [=0]"";General

    HTH

  10. #10
    Join Date
    1st October 2012
    Posts
    1

    Re: Lookup Value Across Multiple Worksheets

    hi,

    this surely works on the same workbook ,
    how about doing the same across different workbooks ?

    thankss

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 6
    Last Post: October 10th, 2012, 23:47
  2. Lookup Function/Formula Across Multiple Worksheets
    By Freak in forum Excel General
    Replies: 8
    Last Post: May 16th, 2008, 03:54
  3. Replies: 14
    Last Post: February 26th, 2008, 00:27
  4. Lookup Across Multiple Worksheets
    By thaj0ka in forum Excel General
    Replies: 2
    Last Post: October 20th, 2007, 10:25
  5. Replies: 5
    Last Post: April 16th, 2007, 18:45

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
  •  
porno