Looking up any match, returning data

[Stacie2410]

I've got a spreadsheet that has over 1700 rows of data. The data has columns
for:

Property # (col A)
Production Period (col C)
Status (col F)

There are more columns of data, but for this particular question, they are
irrelevant.

The property numbers are listed many times, as are the Production Period,
Product Type and Status. In Column G, what I need to do, is create a
function that looks in columns A, C, D and F, and applies the following logic:

Let's say that my formula will go into G8.

If any row in the range matches A8 and C8, and the Status for that row says
"Keep", then I want G8's formula to return "Keep" as well.

There will be many rows in which the rows match A8 and C8, but the status
will be blank. However, even if there is one which does match and the status
is Keep, then I need it to return "Keep".

Is this at all possible?

 

[david rocha]

I've got a spreadsheet that has over 1700 rows of data.  The data has columns
for:

Property # (col A)
Production Period (col C)
Status (col F)

There are more columns of data, but for this particular question, they are
irrelevant.

The property numbers are listed many times, as are the Production Period,
Product Type and Status.  In Column G, what I need to do, is create a
function that looks in columns A, C, D and F, and applies the following logic:

Let's say that my formula will go into G8.

If any row in the range matches A8 and C8, and the Status for that row says
"Keep", then I want G8's formula to return "Keep" as well.  

There will be many rows in which the rows match A8 and C8, but the status
will be blank. However, even if there is one which does match and the status
is Keep, then I need it to return "Keep".

Is this at all possible?

I'm not sure what type of match you're looking for, but something like
this may work:

=IF(AND(A8="123B", C8="MAR2009", F8="Keep"),"Keep","")

you can make it fancier or more foolproof if there may be typos in the
keep column, or variations on what you want to match (e.g. it's
possible to find a single word or characters within a cell).

 

[Eduardo]

Hi,
try
I added to leave the cell blank if the criteria is not met. I assume that in
column A you are comparing with A8 and column C with B8, then in F you will
have keep not sure what you have in column D

=IF(SUMPRODUCT(($A$9:$A$1000=$A$8)*($C$9:$C$1000=$B$8)*($F$1:$F$1000="keep"))>0,"Keep","")

 

[Stacie2410]

Hm, let me try to explain a little differently.

I've got a spreadsheet with over 1700 rows. The columns that I are relevant
to this problem are:

Column A - Property Number
Column C - Production Period
Column F - Status

For column A, the property numbers will be listed many times, it's not a
unique number.

For column C, the production period will also be listed many times.

For column F, some of the cells are null and some of them have the word
"Keep" in them, as a result of a formula in that column which returns the
word "Keep" if data in column E fits a certain criteria.

What I need, is a formula in column G that looks through the entire
spreadsheet to see if there is a row in which column A (Property #) matches
that current rows data in column A, AND the data in column C (Production
Period) matches the current rows data in column C . There will be many
instances where the data in columns A and C both match the data in columns A
and C of the row where the formula resides. What I need it to then do, after
determining that both A and C match, is to check column F of that row, some
of those rows will be blank, but there might be one that says "Keep". If
there is one that says "Keep", then I need it to return the result of "Keep".

Here's an example:

Let's say that my cursor is on cell G21.

What I need the formula in G21 to do is look through the spreadsheet to see
if there's a match to G21 (there will be many many matches), then I need it
to see if, of those matches, there are any that match C21 (again, there will
be multiple matches). Then, of those matches, I need it to look at column F
to see if any of them, even just 1, say "Keep". If not, then leave G21
blank. If there is one though, to say "Keep".

I hope that helps.

 

[Eduardo]

Hi Stacey,
did you try the formula I sent, should be giving you what you are looking for

 

[Stacie2410]

Ah yes, I see that now. The "suproduct" threw me off. I'm testing it now
and so far it looks pretty good, thanks a million, this saves tons of time!