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:

	A	B	C	D	E	F to R	S
1	Jan	Emp1	Name	Name	City	Results	None
2	Feb	Emp2	Name	Name	City	Results	None
3	Mar	Emp3	Name	Name	City	Results	None
4	Jan	Emp1	Name	Name	City	Results	None
5	Feb	Emp2	Name	Name	City	Results	None
6	Mar	Emp3	Name	Name	City	Results	Other
7	Jan	Emp1	Name	Name	City	Results	None
8	Feb	Emp2	Name	Name	City	Results	COBC
9	Mar	Emp3	Name	Name	City	Results	None
		Emp1	Should return "None"
		Emp2	Should Return "COBC"
		Emp3	Should Return "Other"

<colgroup><col span="8"></colgroup><tbody>
</tbody>

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))