Multiple instances of lookup value
 

Can this be done? I have a worksheet that I use to track scores on
the Army physical fitness test. I use the sheet for unit level events
so there are soldiers from 5 different platoons. I would like to pull
the data from this input sheet into a seperate sheet for each platoon
that lists scores by name. The problem is there are multiple
instances of the lookup values(platoon) on the input sheet and I also
need the lookup to return the name which is in column A. I don't
think I can use individual names as lookup values because soldiers
come and go? Is there a way to use the name if it also meets another
condition? Please see example below.

NAME PLT SCORE GO/NO-GO
Johnson 1 300 GO
Smith 2 179 NO-GO
Thomas 3 245 GO
Allen OPS 220 GO
Roberts HQ 250 NO-GO
Myers 1 249 GO

Multiple instances of lookup value
 

Maybe you can try this:

1) Sheet 1 will be the list

NAME PLT SCORE GO/NO-GO
Johnson 1 300 GO
Smith 2 179 NO-GO
Thomas 3 245 GO
Allen OPS 220 GO
Roberts HQ 250 NO-GO
Myers 1 249 GO

-------------------------------
These are the COLUMNs:
A-NAME
B-PLT
C-SCORE
D-GO/NO-GO



2) In Sheet 2, pls do the following:

a) Cell A1 = Johnson
b) Cell B1 = 1
c) =INDEX(Sheet1!C1:C7,MATCH(1,(Sheet1!A1:A7=A1)*(She et1!B1:B7=B1),0))
*Enter with Ctrl+Shift+Enter (CSE) {} will auto appear
d) Result = 300

HTH.

"SGT Buckeye" wrote:

Can this be done? I have a worksheet that I use to track scores on
the Army physical fitness test. I use the sheet for unit level events
so there are soldiers from 5 different platoons. I would like to pull
the data from this input sheet into a seperate sheet for each platoon
that lists scores by name. The problem is there are multiple
instances of the lookup values(platoon) on the input sheet and I also
need the lookup to return the name which is in column A. I don't
think I can use individual names as lookup values because soldiers
come and go? Is there a way to use the name if it also meets another
condition? Please see example below.

NAME PLT SCORE GO/NO-GO
Johnson 1 300 GO
Smith 2 179 NO-GO
Thomas 3 245 GO
Allen OPS 220 GO
Roberts HQ 250 NO-GO
Myers 1 249 GO

Multiple instances of lookup value
 

My apology, please add Cell C1 in item 2c.

2) In Sheet 2, pls do the following:

a) Cell A1 = Johnson
b) Cell B1 = 1
c) Cell C1
=INDEX(Sheet1!C1:C7,MATCH(1,(Sheet1!A1:A7=A1)*(She et1!B1:B7=B1),0))
*Enter with Ctrl+Shift+Enter (CSE) {} will auto appear
d) Result = 300

Multiple instances of lookup value
 

On Sep 18, 1:58 am,
wrote:
My apology, please add Cell C1 in item 2c.

2) In Sheet 2, pls do the following:

a) Cell A1 = Johnson
b) Cell B1 = 1
c) Cell C1
=INDEX(Sheet1!C1:C7,MATCH(1,(Sheet1!A1:A7=A1)*(She et1!B1:B7=B1),0))
*Enter with Ctrl+Shift+Enter (CSE) {} will auto appear
d) Result = 300

Thanks for all the suggestions but I figured out something that works
for me. I copied all the data from the master worksheet to each of
the platoon worksheets. I then used the Record Macro feature of Word
2007 to unprotect the sheets, filter by platoon on each sheet, sort
from high score to low score, and then protect the sheets again. It
works like a charm. Again, many thanks for the suggestions.