I'm wondering if anyone can help me with working out how to add a hyperlink based on certain conditions.

I have two worksheets - Worksheet "Catalogue" has a dropdown list to select which fruit and another worksheet which has information about the piece of fruit selected. There are multiple lines about each of the pieces of fruit. The second worksheet Fruit looks like below:

 apples apples are green apples they come in different sizes apples https://www.infoaboutapples.com/apple.htm bananas are yellow bananas are bent bananas https://www.allaboutbanans.com/yellow.htm pear are squishy pear can be yellow pear https://pearsaregood.com/stick.thm

I have found when I parse this information through INDEX - the links in the doco get removed. This is my formula located on the Catalogue worksheet:
=IFERROR(INDEX(Fruit!\$D\$1:\$D\$1179,SMALL(IF(Catalogue!\$D\$7=Fruit!\$B\$1:\$B\$1179,ROW(Fruit!\$D\$1:\$D\$1179),""),ROW()-8)),"")

Using this formula, I get the correct result but the hyperlinks are no longer hyperlinks. To fix this I created an extra column in the fruit sheet to identify which lines are hyperlinks and modified the formula to be:
=IFERROR(
IF(VLOOKUP(INDEX(Fruit!\$C\$1:\$C\$1066,
SMALL(
IF(Catalogue!\$D\$8=Fruit!\$B\$1:\$B\$1066,
ROW(Fruit!\$C\$1:\$C\$1066),
""),

INDEX(Fruit!\$C\$1:\$C\$1066,
SMALL(
IF(Catalogue!\$D\$8=Fruit!\$B\$1:\$B\$1066,
ROW(Fruit!\$C\$1:\$C\$1066),
""),
ROW()-10))),
"")

I was testing above - In the line where it says "gee whiz - stupid hyperlinks", I did have hyperlink("www.google.com") - just to see if I could get a hyperlink to work. This did work however if I copy the formula to all the lines being outputted, all of them end up being hyperlinks. If I remove the syntax of hyperlink("www.google.com"), then I see "gee whiz - stupid hyperlinks" in the correct spot and none of the other output for the selected fruit is a hyperlink.

This sounds all very convoluted - so if anyone is able to help me figure out how to detect within the array that there is a hyperlink and apply the hyperlink command I would really appreciate it. Or if there is a way for me to parse the information from the fruit workshop and keep the hyperlink intact that would be great too.

Thanks a lot!
Gabby

2. ## Re: using SMALL, INDEX, with IF conditional adding a HYPERLINK

so I think the problem for me is as basic as - using hyperlink() isn't behaving as expected.

eg. using the following data
horse
dog
http://www.animals.com
cow

If I copy the below formula in the column next to the above data to create a hyperlink - all of the data in column 2 becomes a hyperlink regardless of where it has http in the name or not.

The output looks like this:
http://www.animals.com http://www.animals.com

Is this normal behaviour? all of the "no link" cells are also hyperlinks and receive an error of "the address of this site is not valid ..etc" which is true as they aren't valid links. Why would the formula apply a hyperlink to it despite it not meeting the criteria?