I created a formula this morning for checking stock of an item in my excel sheet by searching for the item, then checking for the subitem under that item, and then reporting the stock which is listed next to that sub item. Sub items are repeated but main items are not, so I had to search for the main, then INDEX the array where the sub items are located so I can pull the stock values.

I got everything to work.

Then I decided to put the stock numbers onto another worksheet so I can keep the stock sheet separate from my other worksheets and pull from one master stock instead of copying stock to every sheet where I am finding these items. Then the problems began in converting the formula to pull from that new stock sheet.

Now I am stumped.

My master formula has transformed so far to:
=INDEX(("Stock!"&ADDRESS(MATCH(LEFT(T5,FIND(":",T5,1)-1),Stock!C:C,0),11)&":"&ADDRESS(MATCH("Total "&LEFT(T5,FIND(":",T5,1)-1),Stock!C:C,0),11)),MATCH(LEFT(D5,FIND(" ",D5,1)-1),"Stock!"&ADDRESS(MATCH(LEFT(T5,FIND(":",T5,1)-1),Stock!C:C,0),4)&":"&ADDRESS(MATCH("Total "&LEFT(T5,FIND(":",T5,1)-1),Stock!C:C,0),4)),1)

and instead of the stock value, it returns #VALUE

When I run through calculation steps to find the error it shows:
INDEX("Stock!$K$101:$K$107",MATCH("110v","Stock!$D101:D$107"),1)

and calculates to:
INDEX("Stock!$K$101:$K$107",#VALUE!,1)

However, when I take this formula:
=MATCH("110v",Stock!D101:D107)

and apply it on its own, the value comes out as 2, no problems. Why am I getting a #VALUE! problem with the main formula when inputting the same exact part that's a problem returns a real value?