Index/Match not working


New Member
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!




Well-known Member
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)


Well-known Member
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.


Well-known Member

=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.

Some videos you may like

This Week's Hot Topics