Vlookup checking multiple columns

**caippers** · 05-06-2010, 11:16 AM

I'm trying to return the results of two col_index_num on a vlookup and then use the result of those columns for a T or F statement.

I am using my tab calculation sheet for my lookup value

I then am going to the tab Deferred Segment for my Table_array

Here is where I'm not sure if I should be using a different formula for my data. What I want to do is verify whether there is a Yes in column C or D and if there is a yes return a value of T if not then false or return a value of 1 or 0 I'm not picky either way there.

**NBVC** · 05-06-2010, 11:43 AM

something like this?

=Sumproduct(--(Range1=Criteria),(Range2="Y")+(Range3="Y"))>0

this checks Range1 for your lookup value, then it checks if range2 or range3 has a "Ye" in it on the same row(s).

If yes, it returns TRUE.

if you want 1 or 0, then: =--(SUMPRODUCT(--(I2:I8=B1),(J2:J8="Y")+(K2:K8="Y"))>0)

or if you want T or F: =LEFT(SUMPRODUCT(--(I2:I8=B1),(J2:J8="Y")+(K2:K8="Y"))>0)