Can Excel return multiple correct answers?

[sam]

Good morning.

In all my years, I’ve not come across this. Searched the Board; there’s a
post suggesting drop-down lists, but that would require too much ongoing
manual input (I think).

1) I have 2 criteria that must be met
2) I have a table of data that includes many rows of data that will meet the
criteria, many that will not.

How do I get Excel to extract all correct answers? The problem, as I see it,
is that I input my request for this in one cell, thus limiting myself to one
answer. I’m not so sure the answer is to copy that static criteria down a
bunch in one column, and then do a V-Lookup, because there are multiple
unique 2-criteria data sets with corresponding multiple possible returns.
That means I’d have to copy a bunch of different 2-criteria data sets and
V-Lookups (hundreds).

Do I finally have a reason to learn Access? I have Excel 00, 03, and 07.

I sense I’m gonna learn something new today. Thanks!

 

[Jim]

Check out data>filter>advanced.

HTH

 

[sam]

Hey Jim –

Wanted to get back and acknowledge your advice; been sitting here reading.
This is really interesting.

If I’m reading the 2000 manual correct, it looks like I can have multiple
unique 2-criteria data sets instead of just one, static 2-criteria data set.

And that’s by putting the criteria on the SAME row? And then I can have a
bunch of those unique data sets going down the two columns?

Really appreciate your help so far.

sam

 

[Pete_UK]

Debra Dalgleish has some notes on Advanced Filters here:

http://www.contextures.com/xladvfilter01.html

Easier to follow than in XL Help.

Hope this helps.

Pete

 

[Jim]

That's pretty much the way it works. Start simple. Copy a column heading
from your data to a cell off to the right of the data. Enter your criteria
in the cell below the heading. These two cells are your criteria range.
Then do Data>Filter>advanced and enter your data range and criteria range.
Excel will return all the records in the data that match your criteria.
After you get that down, you can add additional field headings and expand
your criteria.

It's a very strong feature.

HTH
Jim

 

[sam]

This is huge. Thanks to you and Pete.

It occurs to me that that one of my criteria is not as static as I
portrayed. The two criteria are Name and Date.

The table of possible answers obviously includes name and date (as well as 3
other data items on the same row that need to be returned), BUT a correct
return should include any row of data in the Table where there is a name
match and a date that is plus/minus 14 days of the criteria date.

I'll keep reading, but do you know if I can use an IF statement somehow? So
if my criterion date is Jan 01, 2008, which is 39448, any date in the table
of possible answers between 39434 and 39462 is a correct answer. As long as
there is a name match, of course.

 

[Jim]

You can use two date criteria cells. In one, put '>=1/1/08 and in the second
put '<1/15/08. You would put these side by side with the date field heading
above.

HTH