Vlookup using two conditions

    forum.ozgrid.com/index.php?attachment/33089/


    I've tried to simplify my question by using the following example.


    Neighbor: Tom (entered by user)
    Day: Wednesday (entered by user)
    Movie:
    Times Watched:


    Column
    A B C D
    Tom Monday Friday the 13th 5
    Tom Tuesday Jaws 10
    Tom Wednesday Indiana Jones 5
    Tom Thursday The Matrix 8
    Tom Friday Bourne Identity 10
    Fred Monday Friday the 13th 5
    Fred Tuesday Jaws 10
    Fred Wednesday Indiana Jones 5
    Fred Thursday The Matrix 8
    Fred Friday Bourne Identity 10


    I'm trying to create a sheet that will allow me to automatically fill this information. I tried to use a vlookup but I need it to use two conditions, A and B (Neighbor and Day) before it can return the correct results. I want to be able to enter the Name and the Day and have the Movie and Times Watched automatically filled in.


    It's been a few years since my Excel days, and this is eluding me but I know it's an easy answer >_<. I've attached an example sheet so you can see what I'm talking about.

    Re: How would I accomplish this?


    gigawattz welcome with us,
    Can you place your self in the one looking for information in this forum (you few minutes ago), do you think your title could help him!
    Can you chose a more effective one.

    Triumph without peril brings no glory: Just try

    Re: Vlookup using two conditions?


    I have come up with 3 solutions to this for you:

    Firstly I defined a named range called DataTable for your range of data.
    The first lookup for Movie then becomes:

    =INDEX(DataTable,SUMPRODUCT((INDEX(DataTable,,1)=$C$3)*(INDEX(DataTable,,2)=$C$4)*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}),3)

    Time is identical apart from the final 3 becomes a 4 since Time is the 4th column in DataTable
    This is obviously a bit unwieldy since if you add to the table you have to modify the constant array, so a second solution is to add an index column (which I made the first column) which allows:

    =INDEX(DataTable2,SUMPRODUCT((INDEX(DataTable2,,2)=$C$3)*(INDEX(DataTable2,,3)=$C$4)*(INDEX(DataTable2,,1))),4)

    And the 3rd solution is to simply add headers Neighbor, Day, Movie, Time to the data table and then use an Autofilter. This actually has far greater flexibility but may not be what you are looking for.

    Returning a worbook with the 3 solutions on separate sheets.

    Re: Vlookup using two conditions?


    Thank you very much guys, I was able to adapt this into my actual document and it works great! The only problem I have now is when my formulas calculate a cell that doesn't have a value in it, which returns a #N/A and kills all of the remaining formulas. Is there a way for it to assume 0 or not be calculated at all?

    Re: Vlookup using two conditions?


    For example, Niraj, if I wanted to then sum the amount of Times Watched, the formula would error out in your example. Your example is basically identical to the actual sheet I am using, so for the sake of finding the answer, let's assume someone's name would be missing in one of the formulas which would give an #N/A, which would ruin any SUM formulas

    Re: Vlookup using two conditions?


    Whichever version of the lookup you use, you will get some kind of error for a failed search. Put the lookup formula in a cell, say A7, then in C7 (working from your example here):

    =IF(ISERROR($A$7),0,$A$7)

    So if you get an error it returns 0 otherwise it returns the contents of the cell. You could equally do:

    =IF(ISERROR($A$7),"",$A$7)

    For a text value if you wanted to show a blank. Don't do blanks for numerics though or it will bite you one day.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login