Return a text value that is not equal to a text value with a lookup or array

[Allkair]

Good Morning,

Here is what I have:

In column B is a employee number, in columns C through R is a bunch of data that is irrelevance to this need, but then in column S is where another formula returns a value of "None", or another value such as "COBC", or a few other possible items. Below all of this data, also in column B is a list of just the unique employee ID numbers.

What I need is formula/array that will go with the unique employee ID list, look at the tables above and pull out a value for that specific employee ID should a value in a column P be something other than "None". If it helps each employee ID could appear in the table up to 3 times and as few as once. Should the formula/array not find anything other than "None" than "None" should be the value that is shown.

Here is a data example:
[TABLE="width: 512"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F to R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jan[/TD]
[TD]Emp1[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Feb[/TD]
[TD]Emp2[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mar[/TD]
[TD]Emp3[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jan[/TD]
[TD]Emp1[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Feb[/TD]
[TD]Emp2[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mar[/TD]
[TD]Emp3[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jan[/TD]
[TD]Emp1[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Feb[/TD]
[TD]Emp2[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]COBC[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Mar[/TD]
[TD]Emp3[/TD]
[TD]Name[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Results[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Emp1[/TD]
[TD="colspan: 3"]Should return "None"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Emp2[/TD]
[TD="colspan: 3"]Should Return "COBC"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Emp3[/TD]
[TD="colspan: 3"]Should Return "Other"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is appreciated and thank you in advance.

 

[j8h9x]

Dim x As Integer, y As Integer, z As Integer
Dim sRange As String

Const cDATA As String = "Data"
Const cWORK As String = "Work"

On Error Resume Next
Sheets(cWORK).Select
On Error Resume Next
ActiveWindow.SelectedSheets.Delete

Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = cWORK

x = 2
Do
If Worksheets(cDATA).Cells(x, 1) = "" Then Exit Do
Worksheets(cWORK).Cells(x, 1) = Worksheets(cDATA).Cells(x, 3)
x = x + 1
Loop

sRange = "$A$2:$A$" & Trim(Str(x))
ActiveSheet.Range(sRange).RemoveDuplicates Columns:=1, Header:=xlNo

x = 2
Do
If Worksheets(cWORK).Cells(x, 1) = "" Then Exit Do
y = 2
Do
If Worksheets(cDATA).Cells(y, 3) = "" Then Exit Do
If Worksheets(cDATA).Cells(y, 3) = Worksheets(cWORK).Cells(x, 1) And _
Worksheets(cDATA).Cells(y, 8) <> "None" Then
Worksheets(cWORK).Cells(x, 2) = Worksheets(cDATA).Cells(y, 8)
Exit Do
End If
y = y + 1
Loop
If Worksheets(cWORK).Cells(x, 2) = "" Then Worksheets(cWORK).Cells(x, 2) = "None"
x = x + 1
Loop

 

[j8h9x]

this is clumsy but it works

 

[Allkair]

this is clumsy but it works

Thanks j8h9x. I am hoping that it can be done with a formula or an array though. I am pretty sure that it is just some sort of Index & Match combination that I have not yet stumbled across.

 

[Allkair]

This appears to be the solution that I was after! Thanks to all the took a look and tried to help.

=IF(SUMPRODUCT(INDEX(($B$2:$B$110=B114)*($S$2:$S$110<>"None"),,))=0,"None",INDEX($S$2:$S$110,MATCH(1,INDEX(($B$2:$B$110=B114)*($S$2:$S$110<>"None"),,),0),1))