index match multiple criteria

[davelevnt]

Hi I am needing some assistance.

From another excel file, i want to bring into a report the value matching both the GL code and year.. i have put below the formula

in effect bring back the value that matches both gl code (cell m14 - 1-010-100-2) and year (cell d12 - 2023) from within the data field. it should be bringing back 2,121,051 however is #n/a.

If you can please assist.

Dave

Formula.
=INDEX('[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$H$370:$AF$5000,MATCH(1,('[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$AG$370:$AG$5000=$M14)*('[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$H$369:$AF$369=D12),0))

Report - separate file

Data Set in separate file.

 

[Alex Blakenburg]

I haven't tested this since there is quite a bit of setup involved but give this a try:

=INDEX('[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$H$370:$AF$5000,
       MATCH($M14, '[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$AG$370:$AG$5000, 0),
       MATCH(D$12, '[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$H$369:$AF$369, 0))

 

[davelevnt]

I haven't tested this since there is quite a bit of setup involved but give this a try:

=INDEX('[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$H$370:$AF$5000,
       MATCH($M14, '[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$AG$370:$AG$5000, 0),
       MATCH(D$12, '[NEW 2025 Template 10 Year Long Range Plan WIP.xlsx]Trial Bal Data '!$H$369:$AF$369, 0))

Perfect Thanks heaps. your a life saver

 

[Alex Blakenburg]

You're welcome. Glad I could help.