VLookup with formatting

[Lukey]

Hope someone can help as I am not 100% sure what function I need. I have been using a VLookup for some other items on a sheet and think that it may be appropriate here.

I have a list of computers, each with a 3 or 4 character department prefix and I want to match these up with the full department name (if that makes sense)
i.e:

ACC-JBloggs - Accounts
ADM-SBloggs - Admin
SALE-DBloggs - Sales

Is there anything that I can add to the VLookup function that will just lookup anything before the '-' sign ?

Any help much appreciated

Thanks

 

[SkipVought]

Hi,

Just make another column with the PREFIX.

Skip,

_[red][/red]

 

[Rofeu]

Hi,

vlookup will take wildcards, so you can use something like this:

=VLOOKUP("ACC-*",A1:B5,2,FALSE)

to return Accounts.

Cheers,

Roel

 

[Lukey]

Couldn't see the wood for the trees, good idea ; )
Do you know the easiest way to split a column using anyting before the '-' sign ?

Thanks

 

[SkipVought]

Data/Text to columns...

Skip,

_[red][/red]

 

[Lukey]

Cheers Rofeu, problem is that I have about 60 different departments so I really just wanted it to match the prefix to the full dept. name and then ignore the user name.

 

[Rofeu]

Hi,

use a combination of the functions LEFT and FIND (or SEARCH) to get everything before "-", but that's not even necessary when using the wildcard.

Cheers,

Roel

 

[AnotherHiggins]

->Do you know the easiest way to split a column using anyting before the '-' sign ?

You could just use Data > Text to Columns and use - as the delimiter.

Or you could use the following formula in another column:
[tab][COLOR=blue white]=left(a1,find("-",a1)-1)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Rofeu]

You can link to another cell:

=vlookup(A1&"*",B1:C5,2,FALSE)

for example.

You ARE looking up values like "ACC" and "ADM", right?

Cheers,

Roel

 

[KenWright]

Did you see Skip's post re Data / text To Columns? Cleanse your data correctly and it will pay off in spades later on. get the Prefix, the username and the full name all in their own fields and you can then use a number of tools such as Pivot tables etc, and will find you have much more analytical ability at your fingertips.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Lukey]

Thanks for all your help, it's given me loads of options to try. I know what you mean about the original data but it is an import so I wanted to try and do as much as I can with the raw data.

Cheers

 

[xlbo]

If you don't want to split the data, you can nest a couple of functions in the vlookup

this will give you the dept prefix:

=Left(A2,FIND("-",A2)-1)

you can then insert this into a vlookup:

=vlookup(Left(A2,FIND("-",A2)-1),LookupRange,Offset,False)

the formulae will take longer to calc but I use this quite a lot if I don't want to chop up my data - it is a very powerful thing to be able to do....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[pbrodsky]

When dealing with imported data, I frequently find it easy to add extra columns AFTER the imported columns. They can be left in place when data is re-imported (ie. not deleted) and the formulas just copied down as needed.

 

[KenWright]

Yep - I do exactly the same. Never insert them into your data, as it screws up replacing the data, but always put them afterwards, and then all the formulas stay intact and you just have to adjust for extra rows etc.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

...and

If you are using the Data/Import... you can use the Fill down formulas in columns adjacent to data and the formula in the FIRST ROW will ALWAYS fill to the rows of data imported or queried.

Skip,

_[red][/red]

 

[Lukey]

Thanks very much, it's all been a great help and seems to be working a lot more efficiently now !