I have a spreadsheet with 5 worksheets. I want VLookup to read the City and State in the two columns next to the County Column in the custmast worksheet. Compare the city and state to the worksheets AL, FL and GA worksheets and return the correct County and Territory Code found in the Territory worksheet in the County Column and Territory Column in custmast worksheet. I've attached a file for better understanding.
You have a couple of problems with this I think. AL worksheet appears to have Cities in column B and counties in column A but the other two seem to be the other way round (although headers are the same).
You need these to be consistent and for VLOOKUP to work you need cities in one column (e.g. A) and counties in a column to the right (e.g. B).
You also appear to have a space in the AL worksheet name, i.e. AL[space], this complicates things further.....
sort out these problems and you should be able to use a formula like this in custmast K2 copied down the column
=VLOOKUP(E2,INDIRECT(F2&"!A2:B1000"),2,0)
As it stands you need to use this
=INDEX(INDIRECT("'"&F2&" '!a2:a1000"),MATCH(E2,INDIRECT("'"&F2&" '!b2:b1000"),0))
....and it still won't work on all entries
Hi,Originally Posted by nander
try
=IF(ISERROR(VLOOKUP(E2,INDIRECT(F2&"!A:C"),2,FALSE)),"not found",VLOOKUP(E2,INDIRECT(F2&"!A:C"),2,FALSE))
It is recommended that you do not use trailing spaces in your Sheetnames, and (recommended but not necessary) that the three lookup tables be kept in the same format.
The FL table seems to be under some influence of Filter, Data, ShowAll is required to see all entries.
hth
---
added hi daddylonglegs.
forgot to mention, the book has a number of invalid Named Ranges, these should probably be removed.
(looks a little better than my first mis-read attempt at this ?)
---
Last edited by Bryan Hessey; 03-12-2007 at 08:20 PM.
Si fractum non sit, noli id reficere.
Guys thanks for the help. I've corrected the Column A & B mix-up. Removed the trailing space in AL{space} and applied the VLOOKUP formula. However it only works on the AL worksheet. The custmast contains FL and GA counties too. and the Territory column in custmast needs to have a corresponding territory code from the territory worksheet too. Thanks again for your help. I've included the updated file.
Last edited by nander; 03-12-2007 at 09:12 PM.
Hi,
it works from all three sheets on my copy, which formula did you use?
The Territory is a straight VLookup, same format as the standard vlookup.
No file was found attached.
---
Okay, sorry about the missing file. I was to big to attach. I tried the formula again and it works for all AL, FL and GA. Thank you very much. I realize the Territory code is straightforward but would you be so kind as to provide the formula? Like I said I'm not that familiar with the use of vlook and struggle understand the basics. Thanks
Hi,
good to see one bit worked.
The territory, the formula is easy but I have no concept of your 'Territory', you could try
=VLookup(K2,Territory!A:C,3,False)
hth
---
Last edited by Bryan Hessey; 03-12-2007 at 11:14 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks