How to expand Index/Match formula
 

I have the following formula below, but I want the formula to also
match columnD in addition to columnA on my Data! sheet. ColumnA and
ColumnD on Data! needs to match ColumnC and ColumnE respectively
before returning columnE from Data! on my other sheet.


=INDEX(Data!E:E,MATCH(C2,Data!A:A,0))

Any ideas?

How to expand Index/Match formula
 

If I understand correctly:

Array entered** :

=INDEX(Data!E1:E100,MATCH(1,(Data!A1:A100=C2)*(Dat a!D1:D100=C2),0))

Since this is an array formula** you can't use entire columns as range
references unless you're using Excel 2007.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
I have the following formula below, but I want the formula to also
match columnD in addition to columnA on my Data! sheet. ColumnA and
ColumnD on Data! needs to match ColumnC and ColumnE respectively
before returning columnE from Data! on my other sheet.


=INDEX(Data!E:E,MATCH(C2,Data!A:A,0))

Any ideas?

How to expand Index/Match formula
 

This is a great resource:
http://www.contextures.com/xlFunctions03.html

Definitely worth a look!!


Regards,
Ryan---

--
RyGuy


"T. Valko" wrote:

If I understand correctly:

Array entered** :

=INDEX(Data!E1:E100,MATCH(1,(Data!A1:A100=C2)*(Dat a!D1:D100=C2),0))

Since this is an array formula** you can't use entire columns as range
references unless you're using Excel 2007.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
I have the following formula below, but I want the formula to also
match columnD in addition to columnA on my Data! sheet. ColumnA and
ColumnD on Data! needs to match ColumnC and ColumnE respectively
before returning columnE from Data! on my other sheet.


=INDEX(Data!E:E,MATCH(C2,Data!A:A,0))

Any ideas?