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.

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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