Vlookup using 2 conditions

I have a tab called games with the following info these are just two rows,
there are a hundred or so rows):
A B C D
1 ANH # W/L
2 Oct 16 WILD

I have another tab called scores with the following info (these are just two
rows, there are a hundred or so rows):
A B C D E
1 Oct 16 TB 2 WAS 3
2 Oct 16 WILD 4 ANH 1

I would like cell D2 in the games tab to be a W if cell E2>C2 in tab scores.
How do I use a vlookup with two conditions? I need to lookup Oct 16 in the
scores tab and WILD at the same time to return the 4 from cell C2. Thanks

=IF(ISNA(MATCH(A2&B2,scores!A1:A100&scores!B1:B100,0)),"",IF(INDEX(scores!E1
:E100,MATCH(A2&B2,scores!A1:A100&scores!B1:B100,0))>INDEX(scores!C1:C100,MAT
CH(A2&B2,scores!A1:A100&scores!B1:B100,0)),"W",""))

which is an array formula so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jambruins" <[email protected]> wrote in message
news:[email protected]...
> I have a tab called games with the following info these are just two rows,
> there are a hundred or so rows):
> A B C D
> 1 ANH # W/L
> 2 Oct 16 WILD
>
> I have another tab called scores with the following info (these are just
two
> rows, there are a hundred or so rows):
> A B C D E
> 1 Oct 16 TB 2 WAS 3
> 2 Oct 16 WILD 4 ANH 1
>
> I would like cell D2 in the games tab to be a W if cell E2>C2 in tab
scores.
> How do I use a vlookup with two conditions? I need to lookup Oct 16 in
the
> scores tab and WILD at the same time to return the 4 from cell C2. Thanks

If you don't want to use array formulas, you can still use VLOOKUP by
inserting a lookup column to the left of your existing columns on both
sheets. The new A column will push the existing columns to the right. Enter
in the A column =B2&"-"&C2 which gives you Oct 16-WILD. Then in the E column
enter
=IF(VLOOKUP(A2,Scores!A1:F100,6,FALSE)>VLOOKUP(A2,Scores!A1:F100,4,FALSE),"W","")

As you can see you can expand the lookup column to as many fields as you
like. You will have problems if Oct 16 is actually a date data type, as the
formula in the lookup column converts it to text. The solution for that is to
use a more complex lookup formula like =text(B2,"mmm dd")&"-"&C2

Have fun!
Cheers...Terry

"Jambruins" wrote:

> I have a tab called games with the following info these are just two rows,
> there are a hundred or so rows):
> A B C D
> 1 ANH # W/L
> 2 Oct 16 WILD
>
> I have another tab called scores with the following info (these are just two
> rows, there are a hundred or so rows):
> A B C D E
> 1 Oct 16 TB 2 WAS 3
> 2 Oct 16 WILD 4 ANH 1
>
> I would like cell D2 in the games tab to be a W if cell E2>C2 in tab scores.
> How do I use a vlookup with two conditions? I need to lookup Oct 16 in the
> scores tab and WILD at the same time to return the 4 from cell C2. Thanks

thanks bob and terry.

"Terry Harvey" wrote:

> If you don't want to use array formulas, you can still use VLOOKUP by
> inserting a lookup column to the left of your existing columns on both
> sheets. The new A column will push the existing columns to the right. Enter
> in the A column =B2&"-"&C2 which gives you Oct 16-WILD. Then in the E column
> enter
> =IF(VLOOKUP(A2,Scores!A1:F100,6,FALSE)>VLOOKUP(A2,Scores!A1:F100,4,FALSE),"W","")
>
> As you can see you can expand the lookup column to as many fields as you
> like. You will have problems if Oct 16 is actually a date data type, as the
> formula in the lookup column converts it to text. The solution for that is to
> use a more complex lookup formula like =text(B2,"mmm dd")&"-"&C2
>
> Have fun!
> Cheers...Terry
>
> "Jambruins" wrote:
>
> > I have a tab called games with the following info these are just two rows,
> > there are a hundred or so rows):
> > A B C D
> > 1 ANH # W/L
> > 2 Oct 16 WILD
> >
> > I have another tab called scores with the following info (these are just two
> > rows, there are a hundred or so rows):
> > A B C D E
> > 1 Oct 16 TB 2 WAS 3
> > 2 Oct 16 WILD 4 ANH 1
> >
> > I would like cell D2 in the games tab to be a W if cell E2>C2 in tab scores.
> > How do I use a vlookup with two conditions? I need to lookup Oct 16 in the
> > scores tab and WILD at the same time to return the 4 from cell C2. Thanks