Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Help
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Help
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Help
THanks
Based on the same criteria is there any way to add the contents in column 3 as well Thanks "Luke M" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |