Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formulas in Excel

Status
Not open for further replies.

sunbhu082

Programmer
Joined
Feb 16, 2003
Messages
9
Location
US
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
 
In the 3rd Column have:

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

Regards

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

I hope this helps!



Peace! [peace]

Mike

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

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



 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top