Excel formula check multiple condition and if match return hyperlink ...

In my Excel, I have created 2 sheet.

Sheet 1 : folderPath
Sheet2 : recordList

Please refer to the file attached.

In my recordList, I need to have an automatic HYPERLINK on a file/document if a certain cell meets the conditions or criteria as below:

If State match with State in folderPath
If Code match with Code in folderPath
If Date Received match with Year in folderPath

than in Hyperlink cell auto create Link to the said file is created.

Example Hyperlink : d:/path1/path2/path3/IN/M1/23/1001.pdf

How can I achieve that ....

Thank you ...

Regards,
NZ

Try the attachment with an auxiliary column in yellow.

Regards,
Pedro Wave

Originally Posted by pedrowave

Try the attachment with an auxiliary column in yellow.

Regards,
Pedro Wave

Niceeeeeeee .... Btw, can we replace the naming from full path to "Link" ...

Regards.

Ali, the problem is that I work with Excel in Spanish and I have to translate the Excel formulas into English, which is a bit of a hassle.

I'll keep that in mind next time.

Regards,
Pedro Wave

It should be noted that the row number is obtained with the formula in cell recordList!A8:

=MATCH($C$4&YEAR($E8)&$D8,folderPath!$B$7:$B$11 & folderPath!$C$7:$C$11 & folderPath!$D$7:$D$11,0)

Regards,
Pedro Wave

Originally Posted by AliGW

Add a friendly name:

=HYPERLINK(INDEX(folderPath!$E$7:$E$11,$A8)&INDEX(folderPath!$F$7:$F$11,$A8)&$B8&INDEX(folderPath!$G$7:$G$11,$A8),"Link")

e.g.

=HYPERLINK(INDEX(folderPath!$E$7:$E$11,$A8)&INDEX(folderPath!$F$7:$F$11,$A8)&$B8&INDEX(folderPath!$G$7:$G$11,$A8),B8&".pdf")

@pedrowave

Next time, please include the solution in the body of the post as well as in the attachment. Thanks.

Hi @AliGW
Thank you for your help ....

Regards,
NickzNickz

Hi @pedrowave,

Thank you for your help too ...

Regards,
NickzNickz