Index match using sheet name in array

[MinerExcel]

I couldn't find anything that seemed like it could help me.

This is my problem if you choose to accept. I have a workbook of my products inventory. On the Cover sheet I have an array named Products first column is product code and the second is product description. [TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100[/TD]
[TD]1/4" Washed Chips[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]101[/TD]
[TD]State D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]105[/TD]
[TD]1" CCR[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]200[/TD]
[TD]3/8" Clean[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]205[/TD]
[TD]1" Clean[/TD]
[/TR]
</tbody>[/TABLE]
I have a sheet for each product with the product code for the tab name.
I am trying to make a formula that I can use on all sheets that will read the tab name, lookup the description from the array.

For example on sheet 105 I would like the cell value to read 105 - 1" CCR. This is the formula I tried however it returned #N/A

=MID(CELL("filename",AG3),FIND("]",CELL("filename",AG3))+1,256)&" - "&VLOOKUP(INDIRECT(MID(CELL("filename",AG3),FIND("]",CELL("filename",AG3))+1,256)),West!AA1:AB78,2,FALSE)

If anyone has any ideas for me it would be greatly appreciated.

Thank you,

 

[JoeMo]

Try:

=MID(CELL("filename",AG3),SEARCH("]",CELL("filename",AG3),1)+1,256)+0&" - "&VLOOKUP(MID(CELL("filename",AG3),SEARCH("]",CELL("filename",AG3),1)+1,256)+0,West!$AA$1:$AB$78,2,0)

 

[MinerExcel]

Are you kidding me I just needed to make that FALSE a zero.
That Works! That AG3 really doesn't matter does it? It has no value on my sheet.

Thank you

 

[JoeMo]

Are you kidding me I just needed to make that FALSE a zero.
That Works! That AG3 really doesn't matter does it? It has no value on my sheet.

Thank you

If you look closely there's a +0 that makes all the difference. You can swap the 0 in the lookup for FALSE, they are equivalent, and yes you can use any cell address on the sheet with the CELL function.