Create a Hyperlink from an Array Formula

Hello,

I am a basic excel user, mostly using other peoples formula to do what i need, without really understanding what the formulas are doing 100%. (some brutal honesty)

So i have cobbled together a formula that vlookups multiple sheets on a workbook, but I would like to be able to hyperlink the result to the location in the workbook.

the formula that I am using is an array formula, as it looked much tidier than a "simple" one.

{=VLOOKUP(C3,INDIRECT("'"&INDEX(A3:A20,MATCH(TRUE,COUNTIF(INDIRECT("'"&A3:A20&"'!C2:G1200"),C3)>0,0))&"'!C2:G1200"),1,0)}

so it has a index A3:A20 with all the months from January 2019 - December 2020, referencing the tabs that have credits to customers, lots of info.
and searches for the original invoice number, which will be entered in C3, the link can be in C4.

I have created a front page with a simple search box for people to use that searches the whole workbook and returns basic stuff like customer name, date, status etc
pretty simple just adjusting the last number to return the correct info.

Please could someone help me turn this into a hyperlink?

Thank you in advance for even reading this...

Many thanks
Ben

Hi FDibbins,

Many thanks for the response.

I hope i have managed to add a sample workbook (its like an intelligence test that i feel like i'm failing)

Finger crossed.

On the main sheets I have 2 columns with date, as I couldn't get vlookup to go left, this solution is fine for my purposes.

Kind regards
Ben

If the dates are consistent with the dates of the sheet tabs hopefully the following should work

=HYPERLINK("#"&TEXT($D$8,"mmmmyyyy")&"!c"&MATCH($C$3,INDIRECT(TEXT($D$8,"mmmmyyyy"&"!c:C")),0),"CLICK TO SHOW")

TEXT($D$8,"mmmmyyyy") returns DECEMBER2019 in your example

the match function returns the row number by matching the invoice number

Hi davsth,

Many thanks, that is brilliant!!!