Excel help required

Mr_Toad

I'll try and keep this as simple as possible or I'll only end up confusing myself!

I'm gathering data from a number of different sources using Excel. The data is downloaded from several systems and I've been using VLOOKUP to pull some of the data together from the various sources.

My problem is that systems can have more than one name depending on the data source.

Each system has a unique APP_ID

I want to add the APP_ID to the main data sheet by doing a lookup on the name on another sheet with 4 columns;

APP_ID SYS_NAME SYS_AKA FULL_SYS_NAME

As an example I have a system called DCN it has three rows in spreadsheet all have the same APP_ID

APP0001 DCN DCN Firewalls Data Comms Network
APP0001 DCN DCN MK2 Data Comms Network
APP0001 DCN DCN DE Data Comms Network

I want to return APP0001 if the system name on sheet 1matches any of the above i.e. DCN, DCN Firewalls, DCN MK2, DCN DE, or Data Comms Network.

I've never been a big Excel user but I'm OK with basic VLOOKUP and INDEX and MATCH on say 2 cells but this is an OR situation rather than an AND.

Can this be done with a formula or am I going to have to brush up on my very rusty VBA?

Any help appreciated.

EchoLocation

Could you just clarify how your data is laid out.

Four Columns with headings...

A | B | C | D

APP_ID | SYS_NAME | SYS_AKA | FULL_SYS_NAME
APP0001 | DCN | DCN Firewalls | Data Comms Network
APP0001 | DCN | DCN MK2 | Data Comms Network
APP0001 | DCN | DCN DE | Data Comms Network


The main sheet is structured how? Is it simply a single column list of the 3 possible SYS values and you wish to return the APP_ID in the second column? e.g. ...

A

SYS
DCN
Data Comms Network
DCN DE
DCN
DCN
DCN Firewalls
etc.

It looks like you want to look for each of these values in any of the 3 SYS columns to get the APP_ID? Could you clarify this and the above and I'll see what the best approach is.

Mr_Toad

EchoLocation wrote: »

Could you just clarify how your data is laid out.

Four Columns with headings...

A | B | C | D

APP_ID | SYS_NAME | SYS_AKA | FULL_SYS_NAME
APP0001 | DCN | DCN Firewalls | Data Comms Network
APP0001 | DCN | DCN MK2 | Data Comms Network
APP0001 | DCN | DCN DE | Data Comms Network


The main sheet is structured how? Is it simply a single column list of the 3 possible SYS values and you wish to return the APP_ID in the second column? e.g. ...

A

SYS
DCN
Data Comms Network
DCN DE
DCN
DCN
DCN Firewalls
etc.

It looks like you want to look for each of these values in any of the 3 SYS columns to get the APP_ID? Could you clarify this and the above and I'll see what the best approach is.

Thanks for the help.

Absolutely spot on.

The main sheet contains most of the info I require but the system it comes from identifies each record, or row in Excel by a database name and server name. A server can contain one or more databases.

The non techie world identifies a system by it's APP_ID, something us techies rarely use. The common data between systems is the system name so I a VLOOKUP on system name an use that to pick up the APP_ID for the main sheet.

It works for most systems, those that have no alias in the AKA field, but those that have more than one name the lookup doesn't find so I need it to look in all three columns.

I'd go through them manually but these are huge spreadsheets of nearly 200,000 rows!

I normally sit and work this sort of thing out but I'm trying to do this alongside the day job and every time I think I've got a few minutes the phone rings or something fails and I get an alert that requires attention.

Mr_Toad

I forgot to say that I'm using Excel 2010