using SMALL, INDEX, with IF conditional adding a HYPERLINK

[gabbagab]

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:

[TABLE="width: 404"]
<tbody>[TR]
[TD="align: left"]apples[/TD]
[TD="align: left"]apples are green[/TD]
[/TR]
[TR]
[TD="align: left"]apples[/TD]
[TD="align: left"]they come in different sizes[/TD]
[/TR]
[TR]
[TD="align: left"]apples[/TD]
[TD="align: left"]https://www.infoaboutapples.com/apple.htm[/TD]
[/TR]
[TR]
[TD="align: left"]bananas[/TD]
[TD="align: left"]are yellow[/TD]
[/TR]
[TR]
[TD="align: left"]bananas[/TD]
[TD="align: left"]are bent[/TD]
[/TR]
[TR]
[TD="align: left"]bananas[/TD]
[TD="align: left"]https://www.allaboutbanans.com/yellow.htm[/TD]
[/TR]
[TR]
[TD="align: left"]pear[/TD]
[TD="align: left"]are squishy[/TD]
[/TR]
[TR]
[TD="align: left"]pear[/TD]
[TD="align: left"]can be yellow[/TD]
[/TR]
[TR]
[TD="align: left"]pear[/TD]
[TD="align: left"]https://pearsaregood.com/stick.thm[/TD]
[/TR]
</tbody>[/TABLE]

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),
""),
ROW()-10)),Fruit!$C$1:$D$3595,2,FALSE)="hyperlink",
"gee whiz - stupid hyperlinks",


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

 

[gabbagab]

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.
=if(left("a1,4)="http",HYPERLINK(A1),"No link")

The output looks like this:
horse no link
dog no link
http://www.animals.com http://www.animals.com
cow no link

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?