New to EXCEL -Using VLOOKUP -or- IF statement to locate multiple word phase and associated data?

Question

New to Excel and ‘VLOOKUP ‘, hope I’m clear enough -- In my excel workbook, I have a data sheet (Sheet # 4) which consist of four columns of data – Description/ UPC/ Part # / Cost; each description has multiple word –example - ‘PEANUT BUTTER CHIP’ –or- APRON WAIST 25X11 POLYT COTN.
I then have a return sheet (called ‘Search’) where I’m trying to:
1. Have the user enter a word (i.e. form the example above, ‘ PEANUT’)
2. Get a list below it of all the items with ‘PEANUT’ in it….and the information from each row.
How can I use either ‘VLOOKUP or IF Statement to find ‘all ‘items with one of the words in it?

Answer 1

Neither of those would work for what you are doing. Vlookup only gives the first instance of a number or string from the data. If will not work either. Sounds like you need to create a macro that will go to the data and filter it according to your search word. Lets say you put your word in A1 on your search sheet say Sheet5. Put in this macro:

Sub Searchit()

'

''



'

Dim searchword As String

searchword = [A1].Value

searchword = "*" & searchword & "*"

Sheets("Sheet4").Select

Range("A2").Select

Selection.AutoFilter

ActiveSheet.Range("$A$1:$D$4").AutoFilter Field:=1, Criteria1:=searchword _

, Operator:=xlAnd

End Sub

Answer 2

Unfortunately, you can't.



Vlookup will only return 1 value. So that's not an option. You need a macro to do what you want here, I believe.

Answer 3

As stated, you will have to use a VBA routine to do as you wish. It seems that you wish to extract entire rows of date matching the search criteria to sheet 'Search'. I infer that your data sheet is actually named "Sheet4".



Here is one way to do as you wish. Copy the following event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

Cancel As Boolean)

Dim i, LastRow

LastRow = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Search").Cells.ClearContents

Sheets("Search").Range("A1:D1").Value = Sheets("Sheet4").Range("A1:D1").Value

srchFor = UCase(InputBox("Enter the search criteria.", "Search For ?"))

If srchFor = "" Then

Target.Offset(1).Select

Exit Sub

End If

For i = 2 To LastRow

If InStr(UCase(Sheets("Sheet4").Range("A" & i)), srchFor) Then

Sheets("Sheet4").Range("A" & i).EntireRow.Copy Destination:= _

Sheets("Search").Range("A" & Rows.Count).End(xlUp).Offset(1)

End If

Next

Target.Offset(1).Select

End Sub



Select worksheet 'Search' and right click the sheet tab at the bottom.



Select 'View Code' at the bottom.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').



Close the VBE (red button - top right).



Select sheet 'Search' and double click any cell.



Enter the search word(s). You can enter them in any 'case', i.e. PEANUT, peanut, Peanut, or even PeAnUT.



Press ENTER, TAB, or select any other cell. The rows matching the criteria will be copied to the Search sheet. To search again, simply double click another cell.



Note: you may not have to enter the entire search word to extract the appropriate data. For example, entering 'pea' will return the same entries as entering 'peanut'. However, if you have an overlap on the short search criteria, you may return additional 'matching' entries, i.e. 'peach'.

Answer 4

If you don't want VBE use the following

A1 enter word to find

Col C is list to search

E1 =IF(ISNUMBER (SEARCH($A$1,C1)),ROW(),"")

F1 =IF(ROWS($1:1)>COUNT(E:E),"", INDEX(C:C,MATCH(SMALL (E:E,ROWS($1:1)),E:E,0)))

Copy E1&F1 down to match col C