Lookup Function Help

[kylestt]

Hello,

I have tried searching for a related thread but I can't find anything.

What i have is 2 sheets. I want to look up a huge list of acronyms contained in sheet one, column A and verify the acronym is being used in sheet 2, column B. The column which i'm looking for the acronym is a bunch of text strings (sheet 2 column B). I'd like to have it repeat until its checked all the acronyms in column A.

If it finds the acronym, i'd like to return a value of "Found" in sheet 1 column B.

Hope this makes sense and hopefully its an easy question.

Thanks in advance for any help.

Kyle

 

[Aladin Akyurek]

Hello,

I have tried searching for a related thread but I can't find anything.

What i have is 2 sheets. I want to look up a huge list of acronyms contained in sheet one, column A and verify the acronym is being used in sheet 2, column B. The column which i'm looking for the acronym is a bunch of text strings (sheet 2 column B). I'd like to have it repeat until its checked all the acronyms in column A.

If it finds the acronym, i'd like to return a value of "Found" in sheet 1 column B.

Hope this makes sense and hopefully its an easy question.

Thanks in advance for any help.

Kyle

B2, Sheet1

=ISNUMBER(MATCH(A2,Sheet2!B:B,0))+0

Custom format the formula cell as:

[=0]"";[=1]"Found"

If Sheet!B:B is sorted in ascending order...

=IF(LOOKUP(A2,Sheet2!B:B)=A2,"Found","")

 

[VoG]

Hello and welcome to MrExcel.

Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste into the white space on the right

Sub MarkFound()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If IsNumeric(Application.Match(.Range("A" & i).Value, Sheets("sheet2").Columns("B"), 0)) Then .Range("B" & i).Value = "Found"
    Next i
End With
End Sub

Press ALT + F11 to return to your sheet, Tools > Macro > Macros, highlight MarkFound and click the Run button.

 

[kylestt]

Hi guys! Thanks very much for the help.

I have tried both options given thus far in the thread and not sure either are working as I had imagined.

I went in and put for sheet 1, column A:

AA
BB
CC

then in sheet 2 i can put the exact data in column B

AA
BB
CC

It finds this type of stuff and returns a Found value. If i go into sheet 2, column B and put a text string in such as "This is AA" then it can't find the value anymore. the data i'm trying to look through contains lots of text strings so i'd like to be able to quickly glance through for a specific acronym.

Hope this helps a bit more? I really really appreciate the help offer thus far. This is a great forum!

 

[VoG]

Try

Sub MarkFound()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If IsNumeric(Application.Match("*" & .Range("A" & i).Value & "*", Sheets("Sheet2").Columns("B"), 0)) Then .Range("B" & i).Value = "Found"
    Next i
End With
End Sub

 

[kylestt]

Thanks a ton! Worked like a champ!

Very much appreciated!

Kyle