Formulas in Excel

[sunbhu082]

Hey if anyone knows how to do this, just let me know how



I want to see if a value in a cell matches any value in any other column. If it does, I want the value of a third cell, to be placed in the new cell.



Eg:



ABCD ABFG 1234 !@#$ If ABCD in the first cell is found in the 2nd column, then I want !@#$ in this cell

ABCD

 

[JohnAcc]

In the 3rd Column have:

=If(A1=B1,"!@#$","No Match&quot

Regards

Jon

 

[Bowers74]

Try this if you are checking an entire column (A1:A23) for a value in B1:

=IF(SUMPRODUCT((A1:A23=B1)*1)>1,"!@#$","No Match&quot

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[sunbhu082]

Thanks a lot guys.
I figured out that another way of doing it is thru VB scripts.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/10/2003 by ps7549
'
' Keyboard Shortcut: Ctrl+e
'

Dim row1 As Integer
Dim row2 As Integer
Dim value1 As String
Dim value2 As String


row1 = 1
value1 = Cells(row1, 1).Value

While value1 <> Null
row2 = 1
value2 = Cells(row2, 2).Value
While value2 <> Null
If value1 = value2 Then
Cells(row1, 3).Value = test
End If
row2 = row2 + 1
value2 = Cells(row2, 2).Value
Wend
row1 = row1 + 1
value1 = Cells(row1, 1).Value
Wend

End Sub