Announcement

Collapse
No announcement yet.

Vlookup With 2 Conditions

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

  • Vlookup With 2 Conditions

    =VLOOKUP(A2,MATT_INUP!A$2:B$10000,2,FALSE)
    But I would also like it to make sure it finds the one that has a "0" in MATT_INUP!$D$2:$D$10000

    I made the formula below, but it wont work. Im not sure why it looks good to me?!


    =INDEX(MATT_INUP!$A$2:$D$10000,MATCH(1,(MATT_INUP!$A$2:$A$10000=A2)*(MATT_INUP!$D$2:$D$10000="0"),0),4)

    btw...i did the ctl shift enter

  • #2
    Re: Vlookup With 2 Conditions

    Is it a text "0" that you are searching for, if not it would need to look for numeric 0 - no quotes. Try:

    =SUMPRODUCT((A2=MATT_INUP!A$2:A$10000)*(MATT_INUP!B$2:B$10000)*(0=MATT_INUP!D$2:D$10000))

    or

    =SUMPRODUCT((A2=MATT_INUP!A$2:A$10000)*(MATT_INUP!B$2:B$10000)*("0"=MATT_INUP!D$2:D$10000))
    .

    Comment


    • #3
      Re: Vlookup With 2 Conditions

      i still cant get that to work

      i posted a quick little example....
      Attached Files

      Comment


      • #4
        Re: Vlookup With 2 Conditions

        Sorry, SUMPRODUCT won't work, because column B is not numeric. So you do need to use INDEX/MATCH/MATCH.
        .

        Comment


        • #5
          Re: Vlookup With 2 Conditions

          ive been staring at this formula thinking of where to put the match/match...
          yea i have no idea how to do that one.

          =INDEX(MATT_INUP!$A$2:$D$10000,MATCH(1,(MATT_INUP!$A$2:$A$10000=A2)*(MATT_INUP!$D$2:$D$10000="0"),0),4)

          now sure how to setup that second match?

          Comment


          • #6
            Re: Vlookup With 2 Conditions

            Because you have duplicates in column A, the concatenated index/match method is best. See the attachment. You can hide the new column E.
            Attached Files
            .

            Comment


            • #7
              Re: Vlookup With 2 Conditions

              If you want a formula in D2 to just find the first row where MATT_INUP!A$2:A$11 = A2 and where MATT_INUP!D$2:D$11 = 0 and return the corresponding value from MATT_INUP!B$2:B$11 then one way, without helper columns, is

              =INDEX(MATT_INUP!B$2:B$11,MATCH(1,(MATT_INUP!A$2:A$11=A2)*(MATT_INUP!D$2:D$11=0),0))

              confirmed with CTRL+SHIFT+ENTER

              edit:....actually that isn't too far from the formula you suggested in your first post, that would work too, except you need to lose the quotes around 0 (because the zeroes aren't text) and the 4 at the end should be a 2 (because you want to return the value from the 2nd column of the range, column B, not the 4th)...so your formula then would be

              =INDEX(MATT_INUP!$A$2:$D$10000,MATCH(1,(MATT_INUP!$A$2:$A$10000=A2)*(MATT_INUP!$D$2:$D$10000=0),0),2)
              Last edited by daddylonglegs; June 26th, 2007, 07:50.

              Comment


              • #8
                Re: Vlookup With 2 Conditions

                Originally posted by ByTheCringe2
                Because you have duplicates in column A, the concatenated index/match method is best. See the attachment. You can hide the new column E.
                this worked but i want to mess with it some more and try the other guys example before posting back in case I have a question...kinda got working on a different report before i can go back to this one...

                Comment


                • #9
                  Re: Vlookup With 2 Conditions

                  Fair enough.
                  .

                  Comment


                  • #10
                    Re: Vlookup With 2 Conditions

                    Originally posted by daddylonglegs
                    If you want a formula in D2 to just find the first row where MATT_INUP!A$2:A$11 = A2 and where MATT_INUP!D$2:D$11 = 0 and return the corresponding value from MATT_INUP!B$2:B$11 then one way, without helper columns, is

                    =INDEX(MATT_INUP!B$2:B$11,MATCH(1,(MATT_INUP!A$2:A$11=A2)*(MATT_INUP!D$2:D$11=0),0))

                    confirmed with CTRL+SHIFT+ENTER

                    edit:....actually that isn't too far from the formula you suggested in your first post, that would work too, except you need to lose the quotes around 0 (because the zeroes aren't text) and the 4 at the end should be a 2 (because you want to return the value from the 2nd column of the range, column B, not the 4th)...so your formula then would be

                    =INDEX(MATT_INUP!$A$2:$D$10000,MATCH(1,(MATT_INUP!$A$2:$A$10000=A2)*(MATT_INUP!$D$2:$D$10000=0),0),2)
                    =INDEX(MATT_INUP!$A$2:$D$10000,MATCH(1,(MATT_INUP!$A$2:$A$10000=A2)*(MATT_INUP!$D$2:$D$10000=0),0),2)

                    yea this one seems to work pretty good. Thank you, ill add this to my notes to use on double column references in the future!

                    Comment

                    Working...
                    X