Hyperlink index and match???

[Matty5894]

Hi,

Hoping you can help!

looking to create a HYPERLINK formula in conjunction of an INDEX AND MATCH

I want to point at a "Cell" on Sheet1 and point at Row 1 on Sheet2 and if there is a match between the "Cell" and a cell within the top row on Sheet2 create hyperlink.

Anyhelp be greatly appreciated

Thank in advance

Matt

[mcmahobt]

This should be possible; can you supply a sample workbook to see how your data is setup to give more precise help?

[Matty5894]

Hi, Cheers for the assistance

Hopefully the attached example workbook makes sense in what im looking to achieve

Cheers

Example1.xlsx

[SDCh]

Try this (copy down):

Please Login or Register  to view this content.

[Matty5894]

Cheers for the response and works great in the example spreadsheet

When I move the formula into my required workbook it doesn't work? i think its to do with the CHAR part of the function.

An example of the the cell contents to look up would be

"Have you installed the Talkers for today's install?"

Sorry if i've read the function wrong!

Been a fantastic help

[SDCh]

Explanation

Please Login or Register  to view this content.

"#Sheet2!" => name of the worksheet on workbook that contain the data, format: the name of worksheet always between "# and !"
CHAR(64+(MATCH(A2,Sheet2!$1:$1)))&ROW($1:$1) => the result are the name of column that contain word/words on A2 (ie if A2 = A the result of formula are column G1 on your example)
A2 => value on A2 (the result on your example "A")

Note : you must change "Sheet2" into your worksheet name that contain the data


PS : Sorry my english not good, if you hard on read my explanation, hope another member can give you a better one.

[SDCh]

On the attachment, i make a minor change, so if nothing match with value on cell a, it will be empty

[mcmahobt]

A different approach. You'll just need to change the names of Sheet1 and Sheet2 in the formula to your actual sheet names. The formula should be able to drag down through column B on your Sheet1 example. It will also leave the cells blank that return no match from Sheet2.

Placed in Sheet1 of example:

Please Login or Register  to view this content.

Hope this helps!

[Matty5894]

Thanks for all your help! Got it all up and running

Much appreciated!