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

Vlookup using 2 conditions
 

=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" wrote in message
...
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 E2C2 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

Vlookup using 2 conditions
 

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

Vlookup using 2 conditions
 

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