Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

2 Criteria Lookup Without #N/A

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

  • 2 Criteria Lookup Without #N/A

    I am trying to lookup the value in a table based on two criteria. For the purpose of illustration, I attached the file here.
    What I am trying to do is that I want to fill Table 2 with data from Table 1 which match the company and the dates. If all the dates match for the companies, that problem would be much easier, However, as you can see the dates from each company do not match on the same row,totally massed things up.

    In my real file I have 70 companies, any one have a solution for this ???It would be really appreciated for your help.
    Attached Files

  • #2
    Re: Find Value And Return Row Number

    Are you asking for a way to hide or remove the N/A error?

    Comment


    • #3
      Re: Find Value And Return Row Number

      Originally posted by StephenR
      Are you asking for a way to hide or remove the N/A error?
      If so, try,

      B17 and copied down & across,

      =IF(ISNUMBER(MATCH(B$16,INDEX($A$2:$F$13,,ROWS($B$17:$B17)*2-2+1),0)),HLOOKUP($A17,$A$1:$F$13,MATCH(B$16,INDEX($A$2:$F$13,,ROWS($B$17:$B17)*2-2+1),0)+1,0),"")

      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: Find Value And Return Row Number

        not exactly. Sorry for unclear statement of my question.

        The attachted file is justs a simple example of my database, In my real database I have 70 companies, and the data will be updated from time to time from Bloomberg.

        So I am looking for other way to retrive the right data, rather than what I did in the file by selecting the colums one-by-one in the Match() function for each company to find the row number.

        But also thank you to tell me the way to hide the error message, useful too!

        Comment


        • #5
          Re: Find Value And Return Row Number

          rather than what I did in the file by selecting the colums one-by-one in the Match() function for each company to find the row number.
          If each company has two column data then try my formula. You don't need to select each column for each company.
          Kris

          ExcelFox

          Comment


          • #6
            Re: Find Value And Return Row Number

            thanks for your quick reply Kris, I have applied your formula in Cell B 17, no value appears at all.can you work on the file and upload to the forum please?Don't know what went wrong.

            Thanks

            Comment


            • #7
              Re: Find Value And Return Row Number

              I have been using Krish's formula and it works very nicely, see attached.
              Attached Files
              Triumph without peril brings no glory: Just try

              Comment


              • #8
                Re: Find Value And Return Row Number

                Indeed, it works!!!!!!!!!!

                Sorry, earlier I was at work, we use European standard format at work, so the formula didn't work.

                Thanks a lot Kris.
                I am gonna try apply it to my data sheet.

                Comment


                • #9
                  Re: Find Value And Return Row Number

                  Originally posted by linoutandabout
                  Indeed, it works!!!!!!!!!!

                  Sorry, earlier I was at work, we use European standard format at work, so the formula didn't work.

                  Thanks a lot Kris.
                  I am gonna try apply it to my data sheet.

                  You are welcome!!
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: 2 Criteria Lookup Without #N/A

                    It totally works in my database, you made my day Kris.

                    zillions of thanks :D

                    Comment

                    Trending

                    Collapse

                    There are no results that meet this criteria.

                    Working...
                    X