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.

	AA	AB
1	100	1/4" Washed Chips
2	101	State D
3	105	1" CCR
4	200	3/8" Clean
5	205	1" Clean

<tbody>
</tbody>

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.