VLOOKUP does not work both ways. It's setup to check first column for value,
and return corresponding value. You would need a seperate table keyed by TOR
if you want to return a number. A slightly longer formula:

=IF(ISERROR(VALUE($A$4)),INDEX('Dept List'!$A$2:$A$28,MATCH($A$4,'Dept
List'!$C$2:$C$28,0)),VLOOKUP($A$4,'Dept List'!$A$2:$C$28,2,0))

Formula now checks to see if sheet name is a number or text, and then
performs a lookup accordingly.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Curtis" wrote:

I have a source sheet with 3 columns and 28 rows

Column 1 contains numbers (formated as text (i.e. '1400)
Columns 2 Long Description (Toronto)
Column 3 Short Description (TOR)

In another sheet I have a cell which lists the name of the tab formula

=REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")

If the tab is named 1400 my vlookup works, if it is named TOR it does not. I
have formated the vlookup table in text but I can still not get this to work..

I have to beleive it is a formatting issues but since I have formated
everything in text.....

What am I doing wrong

My vlookup formula is =VLOOKUP($A$4,'Dept List'!$A$2:$C$28,2,0)


thanks