Excel function to search for cells that have a certain value, then return value of adjacent cell

Question

In Excel, I'm trying to understand what functions I need to look for a mentioned value in one column. If it finds the value, it goes to another cell in the same row and prints that row's value. I understand the logic of what needs to happen, but not what functions I need to use in Excel to accomplish this.

Using the attached picture as reference, I want to search all of column G "Associated Test item" for just "5". Each time it comes across a "5", I want it to print the value of column A "ROIN" in the same row as the "5". So in the case of the picture, it would only print "573474". This function should also not see the "5" in "65" and return the "65" row column A value.

(https://i.stack.imgur.com/PwaNz.png)

I've tried using the Find/Search functions, but my lack of understanding on how the Excel coding language works has made it frustrating. And I am having trouble finding guides online that answer this specific problem.

Answer 1

You need to use VBA to do stuff like this and make your own Function().

In your case the column G contains text. Then you need to create a function to split the text to an array of integer values - and then test each for the value of 5.

So if you put this in a Module in your project (Alt + F11), you can use it as a new function that returns True or False.

Function Contains5(MyCell As Range) As Boolean
Dim MyArr() As String
Contains5 = False
On Error GoTo Err

MyArr = Split(MyCell.Value, ",")

For x = 0 To UBound(MyArr)
If CInt(MyArr(x)) = 5 Then Contains5 = True: Exit Function
Next
Exit Function
Err:
MsgBox ("Error in Contains5 function")
Contains5 = False

End Function 

Now you can use it in your sheet as a drag and drop function with reference to what you like to return in combination with other Functions like this:

=IF(Contains5(G2);I2;"N/A")