Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Lookup Value Across Multiple Worksheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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, 18:18.

    Comment


    • #3
      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.

      Comment


      • #4
        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, 17:34. Reason: Auto Merged Doublepost
        Kris

        ExcelFox

        Comment


        • #5
          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.

          Comment


          • #6
            Re: Lookup Value In Multiple Worksheets

            Vlookup Across Excel Worksheets

            Vlookup Across Excel Worksheets

            Comment


            • #7
              Re: Lookup Value In Multiple Worksheets

              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.
              Kris

              ExcelFox

              Comment


              • #8
                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, 12:52. Reason: Auto Merged Doublepost

                Comment


                • #9
                  Re: Lookup Value Across Multiple Worksheets

                  Hi,

                  Custom format the cell

                  [=0]"";General

                  HTH
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Lookup Value Across Multiple Worksheets

                    hi,

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

                    thankss

                    Comment


                    • #11
                      Re: Lookup Value Across Multiple Worksheets

                      Welcome to the board, Killkenny - If you have a question, please start your own thread...

                      Give it an accurate and concise title and explain your problem clearly. If you think this thread can help to explain your issue, you can include a link by copying the URL from the address bar of your browser and pasting into your message.

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X