I have some dirty data and I want to arrange it into a cross reference kind of table and be able to filter it later, maybe.

The data looks something like this.

(below represents Range AA1:AC4 - for example.)
Team Depth Player Name
GB QB1 Aaron Rodgers
GB QB3 Graham Harrell
GB QB2 Vince Young

In a different part of the workbook, I want a formula that will take the input "GB" say from cell A1 and the input "QB2" from cell B1 and have the result "Vince Young" populate cell C1.

Hi,

Am Fri, 23 Aug 2013 12:36:31 -0700 (PDT) schrieb :

(below represents Range AA1:AC4 - for example.)
Team Depth Player Name
GB QB1 Aaron Rodgers
GB QB3 Graham Harrell
GB QB2 Vince Young

In a different part of the workbook, I want a formula that will take the input "GB" say from cell A1 and the input "QB2" from cell B1 and have the result "Vince Young" populate cell C1.

try:
=INDEX($AC$1:$AC$100,MATCH(A1&B1,$AA$1:$AA$100&$AB $1:$AB$100,0))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2