EXCEL HELP PLEASE! Search column for specific text, then return corresponding text in from first cell?

Question

Column A contains names and Column B contains fruit types.


I want to search column B for the word "Apple".

If the cells in the column B contains "Apple", then return the text that's in column A.

However, I want it to return a list that shows the names from Column A with no spacing inbetween

For example:

Column A Column B
Anne Apple
John
Jane Apple

I want

Result:
Anna
Jane



No VBA codes please. Only familiar with formulas.
I used if(B:B="Apple", A:A,"")

But not what I'm looking for.

Answer 1

without VBA you could probably use a time consuming SUMPRODUCT() formula or you can make it easy and use a Helper column....

i put a picture up because i added a header column (i would use the filter option)

- A1 has name...your data starts in A2 on down

- B1 has words...your data starts in B2 on down

- C1 has Apple...thats the word you are looking for (you would change it to banana if you want)

- - C2 on gets this formula...then drag it down



=IF(B2=$C$1, COUNTIF($B$1:B2,B2),"")



- D1 gets i want

- - D2 gets this formula...then drag it down



=IFERROR(INDEX(A:A, MATCH(ROW(A1),C:C,0),1),"")



then just change C1, you can copy your list and "Paste Values" to wherever you want.



let me know how it works

Answer 2

Possibly one of the Lookup functions would be able to be finagled to work in the way you want. I'm not the most familiar with LOOKUP, VLOOKUP HLOOKUP MATCH etc, but they do return values from other cells. I think that these functions do one search at a time, and if you want multiple matches there might have to be some form of nested functions, but again, not something I've done on any given weekend.

Answer 3

You could use your formula, then filter on column B for 'Apple', and hide column B. That is not, I am fairly certain, that you would not consider a viable solution.



Here is a simple VBA solution that is easy to implement. This example assumes you wish to extract to Sheet2 and you really want to search for 'apple'.



If your extract sheet is not Sheet2, change the two "Sheet2" references to your actual sheet name, i.e. "Sheet5", "Active", or whatever the name is.



If your key word is not 'APPLE', then change the "APPLE" reference to your actual keyword, making sure it it uppercase.



After any adjustments are made to the code, then copy this event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy')



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim i, LastRow

LastRow = Range("B" & Rows.Count).End(xlUp).Row

Sheets("Sheet2").Range("A:A").ClearContents

For i = 1 To LastRow

If UCase(Cells(i, "B").Value) = "APPLE" Then

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Cells(i, "A").Value

End If

Next i

Target.Offset(1).Select

End Sub



Open your workbook and select the sheet containing your data.



Right click the sheet tab at the bottom and select 'View Code'.



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).



To create a clean new extract, simply double click any cell in the worksheet.



To retain the VBA capability, save the workbook as an Excel Macro-Enabled Workbook.