Index/Match not working

[CerberaJQ]

Morning all,

Apologies for the second Index/Match query this morning... wasn't sure if piggy-backing onto that thread was allowed! I would like the formula to look at two separate cells in one worksheet, compare them to columns A and C in a second sheet and return the value in column E... I have got so far:

=INDEX('E-Campus Data'!$A$2:$E$10000,MATCH($I2,'E-Campus Data'!$A$2:$A$10000,0),MATCH(L$1,'E-Campus Data'!$C$2:$C$10000,0),5)


I know I have made a similar structure work before, but cannot for the life of me work out where I am going wrong here!

Thanks,

Cerbera

 

[Special-K99]

Does this work?

=LOOKUP(2,1/(($I2='E-Campus Data'!$A$2:$A$10000)*(L$1='E-Campus Data'!$C$2:$C$10000)),'E-Campus Data'!$E$2:$E$10000)

 

[Misca]

The basic syntax for INDEX is :

INDEX(array, row_num, [column_num])

Looks like your formula is using the two MATCH functions for the row and column numbers but then there's still the 5 I can't figure out. There is the version of of INDEX that uses the area parameter ( syntax: INDEX(reference, row_num, [column_num], [area_num]) ) as well but looks like you don't have 5 different areas.

 

[Special-K99]

@Misca: I think 5 is the column to return, column E. The MATCHes are in a bit of a muddle.

 

[Misca]

Try:

=INDEX('E-Campus Data'!$E$2:$E$1000,MATCH(I2&L2,'E-Campus Data'!$A$2:$A$1000&'E-Campus Data'!C2:$C$1000,0))

Use Ctrl + Shift + Enter instead of just Enter.

 

[CerberaJQ]

Does this work?

=LOOKUP(2,1/(($I2='E-Campus Data'!$A$2:$A$10000)*(L$1='E-Campus Data'!$C$2:$C$10000)),'E-Campus Data'!$E$2:$E$10000)

Thank you - worked like a charm!

And yes, the 5 in the original formula was trying to return column E!