Index & Matching Functions

[Erik Sauceda via OfficeKB.com]

I have created a workbook with three sheets. The first contains data
pertaining to a project type and a specific ID number that I have assigned
to it.

For Example: 1 Areospace Ground Equipment Complexes
2 Hangers
3 Maintenance Facilities
4 Warehouse
Etc.....

I have another sheet that contains a database of all my firm's completed
projects in which I detail several fields (i.e. project number,
description, location, cost, client, and its Project Type ID#-taken from
the list aforementioned list).

My third sheet is the one that I have the problem with. I am wanting to
type in the Project Type ID# and have Excel sort through the database on
the second sheet and provide me with a list of entries on this sheet that
match that Project Type ID# on the second sheet.

I get it to where I use the Index fuction and Match function nested within
(putting it where the row entry would go) but I only get the first entry
that matches the Type ID not the rest. (In reallity there are serveral
projects that have pertained to the same Type ID but different clients and
different locations)

Question: How can I get it to give me all the entries that match that
specific Type ID that I have entered? Can this even be done in Excel?

Any help? Thanks!

E-mail: (e-mail address removed)

 

[PeterAtherton]

I have created a workbook with three sheets. The first contains data
pertaining to a project type and a specific ID number that I have assigned
to it.

For Example: 1 Areospace Ground Equipment Complexes
2 Hangers
3 Maintenance Facilities
4 Warehouse
Etc.....

I have another sheet that contains a database of all my firm's completed
projects in which I detail several fields (i.e. project number,
description, location, cost, client, and its Project Type ID#-taken from
the list aforementioned list).

My third sheet is the one that I have the problem with. I am wanting to
type in the Project Type ID# and have Excel sort through the database on
the second sheet and provide me with a list of entries on this sheet that
match that Project Type ID# on the second sheet.

I get it to where I use the Index fuction and Match function nested within
(putting it where the row entry would go) but I only get the first entry
that matches the Type ID not the rest. (In reallity there are serveral
projects that have pertained to the same Type ID but different clients and
different locations)

Question: How can I get it to give me all the entries that match that
specific Type ID that I have entered? Can this even be done in Excel?

Any help? Thanks!

E-mail: (e-mail address removed)

Erik

A database is better, but I played around with this in Excel a while ago.
The solution seems to work but does not recognize every (invoice number). It
Uses INDEX, MATCH LOOKUP and Address functions.

If your are interested I'll send you a copy of the file; the formulas are
hard to explain and you'll find it easier to examine the file

Peter

e-mail (e-mail address removed)
remove the NOSPAM

 

[RagDyer]

See if you can work off of something like this:

Sheet2,
A1 = label "Proj I.D.Num."
B1 = label "All Proj Names"
A2:B100 = datalist

Sheet3,
A1 = cell to enter Proj. I.D.Num to search for.
Enter this *array* formula in B1:

=INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$100=$A$1,ROW($A$1:$A$99)),
ROW(A1)))
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead
oft the regular <Enter>, which will *automatically* enclose the formula in
curly brackets, which *cannot* be done manually.

NOW, you copy the formula down Column B as *needed*.

*NEEDED* means, just how many returns do you expect ... how many times is a
Project ID number repeated in your datalist?

You probably don't know exactly, so ... you should copy down more cells then
you think you need.
When this formula runs out of data to match, it returns a #NUM! error.

Therefore you will *always* want to see at least one #NUM! error message, to
insure that you have returned all possible matches.
If no error is returned, drag down the formula until you *do* see one.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Erik Sauceda via OfficeKB.com]

Thanks....let me play around with these and I will let you know how things
work out....

Erik

 

[Erik Sauceda via OfficeKB.com]

RadDyer,

Can you explain a bit more on your suggesstion? I am trying it out but I
don't understand the process.

Thanks

 

[RagDyer]

Did you create a test data list on Sheet2, and copy the formula I posted to
B1 of Sheet3, and enter a Proj. ID number in A1 of Sheet3, that you *know*
exists *more then once* in your test data list?