Show formula result string location along with the result | Google Sheets

Question

I'm brand new here and noticed stack overflow has all the best answers online. So thought I'd try ask a question myself.

This is what I'd like to do:

Current formula to find data to match search term in A5:

=QUERY({'Jan 2023'!A1:M;'Feb 2023'!A1:M}, "select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13 where Col2 = '"&A5&"'",0)

When the query gets a match to A5 and displays the result:

current result string example: 13-11-2023, Tigosse, 240

Desired final result: 13-11-2023, Tigosse, 240, **'Feb 2023'!A25:C25**

Where 'Feb 2023'!A25:C25 is the actual location of the found data

Sample spreadsheet: https://docs.google.com/spreadsheets/d/1vCdQ5TKfp7dv_qCM-frUGhLWjoXBR2tYpeiDl7vixYU/edit#gid=357764169

TIA

Answer 1

You may try:

=let(combined_,reduce(,tocol(P:P,1),lambda(a,c,vstack(a,let(Σ,indirect(c&"!A1:M"),hstack(Σ,index(c&"!A"&sequence(rows(Σ))&":M"&sequence(rows(Σ)))))))),
     query(combined_, "where Col2 = '"&A5&"'",0))

• the tab names are accommodated in Column_P & the reduce part of the formula automatically combines all the tabs data ready for query
• Column_P is text-formatted (where tab names are saved)