Need Help - Combo If and Vlookup?

[acmurdah]

Need some help. I assume I need to do a combination of an IF statement and Vlookup to accomplish this, but I can't figure it out.

In the example below, I have two worksheets with the first containing employee information and department ID's from our enterprise system. The department ID's are a combination of numbers and text like Column O below. The second worksheet is a complete list I created manually of all department ID's (numbers only approx 200) and the high level department name.

I want column P below to populate with the word "Legal" from worksheet 2 if it is within the department ID range btw 12160 to 12190.

Can someone help me out please?

Worksheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Dept ID (Column O)[/TD]
[TD]Department (Column P)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="width: 257"]12160 - Claims Bureau[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="width: 257"]12190 - General Counsel Secretary Stff[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Dept ID (Column A)[/TD]
[TD]Department (Column B)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="width: 257"]12160[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Legal[/TD]
[/TR]
[TR]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="width: 257"]12190[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Legal[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mackers]

Hi

One solution:

=INDEX(Sheet2!B$2:B$100, MATCH(--LEFT(O2,FIND(" ",O2)-1),Sheet2!A$2:A$100,[COLOR=#FF0000]0[/COLOR])[COLOR=#FF0000][/COLOR])

I have performed an exact match (the red 0, for inexact you want a 1) - is this what you want? If not, you can either perform an inexact match or I can write another formula (please provide more example data if this is the case).

The LEFT/FIND construction cuts the number out of the Dept ID in column O, then compares it with column A of sheet 2. Note that it is good practice to hold the department ID in a cell separately from the " - Claims Bureau" or " - General Counsel Secretary Stff" part of the string, as you wouldn't have to do this operation.

Change the cell references to match your data.

Hope that helps

Mackers

 

[acmurdah]

Thank you Mackers, this looks looks like its working perfectly!!