INDEX & MATCH function with multiple criteria

[The Chad]

I have a problem determining the best solution to look at 2 different columns in Sheet1 (Status WS) and compare against Sheet2 (OrgNameConversion) that will have similar column data so I can select the data from another columns/cell. This is required to convert naming conventions.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Sheet1 (Status WS) has Columns A-N
Column C is called <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:PlaceName w:st="on">Cost</st1:PlaceName> <st1:PlaceType w:st="on">Center</st1:PlaceType></st1lace>
Column E is called Org Unit Name
Column G is where I want the Group Name to go
Column H is where I want the modified Org Unit Name to go
<o> </o>
The rows in Sheet1 identifies personnel that have completed different training modules (sometimes several modules). In excess of 1000 lines.
<o> </o>
The issue is that data in Column E is quite different to how groups are set up. In this case I am using Sheet2 (OrgNameConversion) to compare against Sheet1 and provide a modified Org Unit Name to Sheet1 Column G.
<o> </o>
Sheet2 (OrgNameConversion) has columns A-F
Column B contains <st1lace w:st="on"><st1:PlaceName w:st="on">Cost</st1:PlaceName> <st1:PlaceType w:st="on">Center</st1:PlaceType></st1lace> data
Column C contains Org Unit Name data
Column E contains Group Name
Column F contains modified Org Unit Name
<o> </o>
There are approx 150 rows in Sheet2.
<o> </o>
I am using Excel 2003.
<o> </o>
The formula (array) that I am working on at the moment is
<o> </o>
{=INDEX('Org Name Conversion'!B:F,(MATCH('Status WS'!C5&'Status WS'!E5,'Org Name Conversion'!B:B&'Org Name Conversion'!C:C,0)),4)}
<o> </o>
Note this is not yet addressing the need for Column H is Sheet1.
<o> </o>
Any ideas appreciated.
<o> </o>

 

[Aladin Akyurek]

The formula you have won't succeed on Excel 2003 for the whole column references are not allowed in that type of formulas. Try rather...

Control+shift+enter, not just enter:

=INDEX('Org Name Conversion'!$E$2:$E$100,
  MATCH(1,IF('Org Name Conversion'!$B$2:$B$100=C5,
   IF('Org Name Conversion'!$C$2:$C$100=E5,1)),0))

By the way, it's unclear what problem you are addressing. Also, try to avoid using two shaat names for the same sheet (e.g., Sheet2 and Org Name Conversion).