Index-Match Multiple Criteria From Same Table

[MyCon]

Hi,

Attached is an example file in which I believe, I want to use the Index / Match functions.

As you will see - Table 1 - Far right of worksheet has a list of projects & teams with proposed Not to Exceed (NTE) proposals.

From this table, I want to extract its inputs & classify them under my main table per projects & teams

1st NTEs - Using the following seems to work well to get 1st NTEs (proposals)

=IF($D$13="","N/A",INDEX($AG$15:$AU$50,MATCH($D$13,$AF$15:$AF$50,0),MATCH(B15,$AG$12:$AU$12,0),MATCH($D$14,$AG$14:$AU$14,0)))


To get, 2nd NTEs, I would think I can use the same function & just re-direct the lookup values but for whatever reasons, it's not working for me.

See example

Index-Match Multiple Criteria From Same Table.xlsm supposed to be
Index-Match Multiple Criteria From Same Table.xlsm

Or - Change, ".doc" to ".xlsm"
Thanks

 

[NARAYANK991]

Hi ,

Try this formula in D15 , and copy it across and down.

=IF(OFFSET(D$13,,-MOD(COLUMN(D13),3)+1)="","N/A",INDEX($AG$15:$AU$50,MATCH(OFFSET(D$13,,-MOD(COLUMN(D13),3)+1),$AF$15:$AF$50,0),MATCH($B15,$AG$13:$AU$13,0)+MATCH(D$14,$AG$14:$AU$14,0)-1))

Narayan

 

[MyCon]

Hi NARAYANK991,

Wow!

This is a funky & complex looking formula!

However! It seems to work well!

What is all this?

=IF(OFFSET(D$13,,-MOD(COLUMN(D13),3)+1)

Now, I'll try to use in my project file to verify.

Thanks for the assistance.