Comparing cells

[ccrique]

(see image above) I want to compare each cell in column B to the values in columns I and J. And if a cell in column B contains the same value as a cell in column I or J I want column D to give me the same value that I have in the cell in column K that is in the same row as the value in column I or J that matched with the cell in column B.

So as a result I want the following:

How di I make this happen ?

 

[Georgiboy]

Could use a small UDF as below:

Function GetVal(str As String)
    GetVal = Cells(Range("I:J").Find(str, , , xlWhole).Row, 11).Value
End Function

And use it as below:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2		Dave	100						Dave		100
3		Paul	200							Paul	200
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=GetVal(B2)

 

[ccrique]

Could use a small UDF as below:

Function GetVal(str As String)
    GetVal = Cells(Range("I:J").Find(str, , , xlWhole).Row, 11).Value
End Function

And use it as below:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2		Dave	100						Dave		100
3		Paul	200							Paul	200
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=GetVal(B2)

And what do I do if Dave or Paul show up more then ones in colums I and J. And I want column C( in your example ) to give me all the values of column k corresponding to the values in column I an J that macthed the values in column C?