Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Vlookup With 2 Conditions

  1. #1
    Join Date
    29th December 2005
    Posts
    200

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

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

  3. #3
    Join Date
    29th December 2005
    Posts
    200

    Re: Vlookup With 2 Conditions

    i still cant get that to work

    i posted a quick little example....
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

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

  5. #5
    Join Date
    29th December 2005
    Posts
    200

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    .

  7. #7
    Join Date
    24th April 2007
    Location
    England
    Posts
    675

    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 at 07:50.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    29th December 2005
    Posts
    200

    Re: Vlookup With 2 Conditions

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Vlookup With 2 Conditions

    Fair enough.
    .

  10. #10
    Join Date
    29th December 2005
    Posts
    200

    Re: Vlookup With 2 Conditions

    Quote 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!

    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. Vlookup With 2 Cells As Conditions
    By spinster in forum EXCEL HELP
    Replies: 4
    Last Post: February 11th, 2012, 00:03
  2. Vlookup Based On Two Conditions
    By Davidoff in forum EXCEL HELP
    Replies: 6
    Last Post: January 12th, 2011, 01:30
  3. Vlookup Based On Results From A Vlookup
    By pzarate in forum EXCEL HELP
    Replies: 1
    Last Post: June 9th, 2007, 03:49
  4. IF, Vlookup, Indirect & Sum With Conditions
    By Timbo in forum EXCEL HELP
    Replies: 11
    Last Post: November 9th, 2006, 01:02
  5. vlookup with conditions
    By lorkmc in forum EXCEL HELP
    Replies: 6
    Last Post: June 9th, 2005, 00:06

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