VLookups with Multiple Results

[rjbinney]

I'm trying to find a more efficient way of running a couple of linked tables. For right now, using Excel (as opposed to Access) is a MUST.

I have a list on a worksheet of about 1300 employees. Among other information, there is a column for "Training". Let's call this sheet "Master".

I have another worksheet with a list of 850 employees. Let's call this worksheet "Specials".

About half the employees on Specials are also on Master.

All employees on the Master sheet have attended either [L]eadership training or [C]apability development. SOME have attended BOTH. Each record of training is a new row on Master. So you may have:
- Mick, L
- Keith, C
- Charlie, L
- Charlie, C


I need to indicate on the Specials sheet which training an employee attended, according to "Master". Since some have attended both, I need to determine that.

What I'd like to happen is:

On "Specials", have one cell on each row that is blank if the employee does NOT appear in "Master"; return "L" if (s)he attended Leadership ONLY, return "C" if (s)he attended Capability Development ONLY, and "CL" if BOTH.

I've tried the array/index/match options, but that doesn't lay out quite right.

For a variety of reasons, I'd like to keep "Master" consolidated, and not create a "Master Leadership" and "Master Capability", although I know I could make that work.

Thanks.

 

[energman58]

Yes lots of VLOOKUPs and HLOOKUPs will slow down your spreadsheet

You can replace them with a combination of INDEX and MATCH:

Match finds the position of a value in a vector (single row/column) array and Index returns the value from the row and column you specify so if you have a table say of costs depending on two parameters (clour and size say) you can find the value for say blue and 6 by doing two matches inside an index formula which avoids the 'nested' problem in a lot of cases and makes a real improvement in speed.

INDEX and MATCH are also a bit faster than VLOOKUP in any case for some mystery reason (although you would think that doing two things instead of one would be slower) but it isnt a massive improvement