+ Reply to Thread
Results 1 to 4 of 4

Excel formula Question

  1. #1
    Lucas
    Guest

    Excel formula Question

    I'm trying to look at a range of cells based on two criteria, date and team.
    If both the date and team show up I want the opponent to show up. Not sure
    how to use multiple vlookups for two criteria and if they both match bring
    back the appropriate response. In addition I want the value to be null if
    there is no match. Can anyone provide some thoughts. I've tried to use
    multiple vlookups and if then statements but its not working.


  2. #2
    Toppers
    Guest

    RE: Excel formula Question

    Try something like this:


    =index(sheet2!$c$1:$c$100,
    match(1,(a2=sheet2!$a$1:$a$100)*(b2=sheet2!$b$1:$b$100),0))
    (one cell)


    This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
    it
    correctly, excel will wrap curly brackets {} around your formula. (don't type
    them yourself.)

    Adjust the range to match--but you can't use the whole column.

    This returns the value in Sheet2 column C when column A and B (of
    Sheet2) match A2 and B2 of the sheet (say Sheet1) with your values.


    So A2=Date, B2=Team to be matched while Sheet2 columns A,B and C are Date,
    Team and Opponent

    HTH

    "Lucas" wrote:

    > I'm trying to look at a range of cells based on two criteria, date and team.
    > If both the date and team show up I want the opponent to show up. Not sure
    > how to use multiple vlookups for two criteria and if they both match bring
    > back the appropriate response. In addition I want the value to be null if
    > there is no match. Can anyone provide some thoughts. I've tried to use
    > multiple vlookups and if then statements but its not working.
    >


  3. #3
    Biff
    Guest

    Re: Excel formula Question

    Hi!

    Try this:

    G1 = lookup_value = some date
    H1 = lookup_value = some team
    A1:A20 = date range
    B1:B20 = team range
    C1:C20 = opponent range

    Formula entered as an array using the key combination of CTRL,SHIFT,ENTER:

    =IF(ISNA(MATCH(1,(A1:A20=G1)*(B1:B20=H1),0)),"",INDEX(C1:C20,MATCH(1,(A1:A20=G1)*(B1:B20=H1),0)))

    Biff

    "Lucas" <Lucas@discussions.microsoft.com> wrote in message
    news:8B5998DA-3420-46E4-A472-BBA5FAACD9E8@microsoft.com...
    > I'm trying to look at a range of cells based on two criteria, date and
    > team.
    > If both the date and team show up I want the opponent to show up. Not
    > sure
    > how to use multiple vlookups for two criteria and if they both match bring
    > back the appropriate response. In addition I want the value to be null if
    > there is no match. Can anyone provide some thoughts. I've tried to use
    > multiple vlookups and if then statements but its not working.
    >




  4. #4
    jjenkins
    Guest

    RE: Excel formula Question

    =IF(AND(TRUE, TRUE),
    VLOOKUP(lookup_value,table_array,col_index_num)" ")

    My first shot at this was using the above statements. Inserting the VLOOKUP
    into the TRUE, TRUE parts of the IF(AND()).

    The trick is to get the VLOOKUP to return TRUE or FALSE depending on what it
    finds.... or something like that.

    I'd like to work on this but let me know if the following is and example of
    the table in which you are referencing.

    Team Date Opponent
    Lakers 06/07/06 Celtics
    Lakers 06/08/06 Suns
    Lakers 06/10/06 Suns
    Celtics 06/07/06 Lakers
    Pistons 06/07/06 76ers

    and so on...

    "Lucas" wrote:

    > I'm trying to look at a range of cells based on two criteria, date and team.
    > If both the date and team show up I want the opponent to show up. Not sure
    > how to use multiple vlookups for two criteria and if they both match bring
    > back the appropriate response. In addition I want the value to be null if
    > there is no match. Can anyone provide some thoughts. I've tried to use
    > multiple vlookups and if then statements but its not working.
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1