How to expand Index/Match formula

[Chris]

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?

 

[T. Valko]

If I understand correctly:

Array entered** :

=INDEX(Data!E1:E100,MATCH(1,(Data!A1:A100=C2)*(Data!D1100=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)

 

[ryguy7272]

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

Definitely worth a look!!


Regards,
Ryan---