Announcement

Collapse
No announcement yet.

Return Value After Matching Two Variables

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Return Value After Matching Two Variables

    Hi

    I am attaching a sheet wherein I have tried to match two variables and to return the appropriate value. But I find the formula returns the value which one cell below the correct answer. Can anyone help?

    Warm Regards
    Srinivasan K
    Attached Files

    K. Srinivasan

  • #2
    Re: Return The Value After Matching Two Variables

    Not sure the syntax is right here, but as you have it, the problem is caused by matching the "mum" in D9. I would use SUMPRODUCT:

    =SUMPRODUCT((D9:D14=D18)*(E9:E14=D19)*(F9:F14))
    Last edited by Dave Hawley; December 11th, 2006, 10:06.
    .

    Comment


    • #3
      Re: Return The Value After Matching Two Variables

      Hi

      Thanks. As you said, Sumproduct will work here only if I have unique values (no repeated values for a same combination). If I have multiple values for a combination, this will result in throwing the sum of values.

      If I want to display the value for the fist appearance of this combination, how can I achieve it.

      Do you remember, you said about looping Vlookup up one after the other for a same kind of scenario, long back. If you could explain how Lookup formulae can be looped, it would be of much use.

      I also would like to know, what makes this function to throw a value which is exactly below the right answer.

      K. Srinivasan

      Comment


      • #4
        Re: Return The Value After Matching Two Variables

        I can't see how this should work - it's not a formula I use. However, I CAN see what is wrong with your formula. You are using two MATCH functions connected with a + sign. Al this does is add the row numbers, and, since the first match of "mum" is in D9, whose index is 1, your result is coming from (5+1) = row 6.

        The nesting of MATCH is dealt with here http://www.ozgrid.com/Excel/left-lookup.htm
        and shows the MATCH functions connected with a comma. Also, note that these MATCHs are specifying the row number in an array. In your case the array would be D9:F14, and you would need to specify a column number, 3. You are using a single-dimension array, F9:F14, and defaulting the column number, and it may be that this form of INDEX doesn't work this way - I can't get it to work anyway. Perhaps someone with more exoerience can sort this out.
        .

        Comment


        • #5
          Re: Return The Value After Matching Two Variables

          Personally I would base a Pivot Tables off a Dynamic Named Range and move LOC and SYS columns into the page field.

          Or, in Enter =D9&E9 in C9 and Fill Down. Then use See VLOOKUP and/or INDEX/MATCH

          Comment


          • #6
            Re: Return The Value After Matching Two Variables

            Hi Srini,

            =INDEX(F9:F14,MATCH(D18&D19,D9:D14&E9:E14,0))

            To Enter the array formula hold down Ctrl and Shift while pushing Enter.

            HTH
            Kris

            ExcelFox

            Comment


            • #7
              Re: Return Value After Matching Two Variables

              Great Thanks to KK, Dave and BytheCringe2.

              HAVE A GREAT DAY.

              K. Srinivasan

              Comment


              • #8
                Re: Return The Value After Matching Two Variables

                Originally posted by Krishnakumar View Post
                Hi Srini,

                =INDEX(F9:F14,MATCH(D18&D19,D9:D14&E9:E14,0))

                To Enter the array formula hold down Ctrl and Shift while pushing Enter.

                HTH
                Dear Krishnakumar
                I have a same problem but I have many equals data and I want to find the largest or smallest value. Below is problem that you solved, but assume we have another row with same data on first and second, now I want to find 22 not 19.
                this is the problem befor(solved):

                mum acs 12
                koc acs 13
                chn acs 15
                mum bom 19
                koc bom 20
                chn bom 11








                And this is my problem:

                mum acs 12
                koc acs 13
                chn acs 15
                mum
                bom 19
                koc bom 20
                chn bom 11
                mum
                bom 22

                Sincerely Yours
                Masoud
                Attached Files

                Comment


                • #9
                  Re: Return Value After Matching Two Variables

                  Please do not post questions in threads started by other users.

                  Start your own thread, give it an accurate and concise title and explain your issue fully. If you think any other thread can help clarify your problem then you can include a link by copying the URL from the address bar of your browser and pasting into your message.

                  Comment

                  Working...
                  X