# Lookup Values according to 2 criteria, with duplicate values in both.

1. ## Lookup Values according to 2 criteria, with duplicate values in both.

Hello - I'm trying to work out a formula that will lookup the Team Name and Position and return the Names, horizontally.

For example:

QB QB QB QB QB RB RB WR
Chiefs- Jim Tom Jeff Aaron Steve
Panthers- Mike Connor Erick Andrew Nick

Data:

Team Position Name
Chiefs QB Jim
Chiefs QB Tom
Chiefs QB Jeff
Panthers QB Mike
Panthers QB Connor
Chiefs RB Aaron
Chiefs RB Steve
Panthers WR Nick
Panthers QB Erick
Panthers QB Andrew

I've developed the following formulas but am still encountering issues with both.

1) =IF(COLUMNS(\$G2:G2)>\$B\$21,"",INDEX(\$C\$2:\$C\$11,SMALL(IF(\$A\$2:\$A\$11=\$F\$2,ROW(\$A\$2:\$A\$11)-ROW(\$A\$2)+1),COLUMNS(\$G2:G2))))

** I cannot get this formula to differentiate between teams

2) =INDEX(\$C\$2:\$C\$11,MATCH(\$F5&G\$1,INDEX(\$A\$2:\$A\$11&\$B\$2:\$B\$11,),0))

** I cannot figure out how to nest a SMALL formula in here in order to ignore duplicates and take the next values

2. ## Re: Lookup Values according to 2 criteria, with duplicate values in both.

Try copy / pasting and array-entering this in G2. Fill down and across to L3.
Formula:
`Please Login or Register  to view this content.`
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

3. ## Re: Lookup Values according to 2 criteria, with duplicate values in both.

Dave - You've just cured be headache that's been lingering for the past week. Thank you. One question - Is there a way to modify the formula so that I can copy past cell L3?

Thanks again, Dave.

4. ## Re: Lookup Values according to 2 criteria, with duplicate values in both.

Originally Posted by dys5315
.............. One question - Is there a way to modify the formula so that I can copy past cell L3?
Yes. If you can remove the IF > B21 condition then the formula would be (array-entered again)
Formula:
`Please Login or Register  to view this content.`

When it runs out of data it will return blanks. This one also returns the WR in the Panthers set.

5. ## Re: Lookup Values according to 2 criteria, with duplicate values in both.

Thank you!!!!

6. ## Re: Lookup Values according to 2 criteria, with duplicate values in both.

You're welcome, and thanks for the rep.

There are currently 1 users browsing this thread. (0 members and 1 guests)