Microsoft Excel and VLOOKUP results for text?

Question

Hi, I'm trying to calculate the number of time a word appears in a log file using MS Excel. The word can appear as an exact match or in a string of text. For example if I has a .xls file with two columns with the following

red bananas        (100)
Apples              (20)
Green Bananas       (50)
Bananas             (10)
Orange              (40)

I would like to know that all references to bananas = 160 (100+50+10).

Can anyone help with the vlookup function in MS Excel to help me calculate this. I don't know how to overcome the fact that a word can appear on its own or in a string of text

Anonymous · Accepted Answer

You don't need VLookup here you need another function, which is countif..

if your column in C, put this function in D4
=COUNTIF(C:C;"*banana*")

and enjoy

remeber, if you like it, I want my 10 points...

I am the XLMan

WhoMe · Answer

This is one of those interesting problems that can also be solved with something deceptively simple called an Array Formula.  Just using a regular VLOOKUP or COUNTIF won't work. 

Let's assume your fruit names are in column A and the numbers are in column B. Enter the following formula:

=SUM(IF(ISERROR( SEARCH("Bananas", A1:A5)), "", B1:B5))

*IMPORTANT*
When you enter the formula, enter it not by pressing the ENTER button, but by pressing CTRL-SHIFT-ENTER.  (In other words press and hold down control, shift, and then hit enter.)  You will see that curly brackets are inserted around the formula distinguishing it as an Array formula.

This will return 160, which is your desired sum.  SUMIF with "*banana*" that Makiavel mentions will work well too.  You should select his answer.

calvani · Answer

Vlookup Text

Makiavel · Answer

Use one of these formulas:

CountIf
if you want the cell where this formula is to show the number of times bananas appear (in the example=3)

SumIf
if you want the cell where this formula is to show the sum of all the cells with bananas on it (in the example=160)

Follow the formula wizard to build it properly.

Makiavel

Michael C · Answer

=COUNTIF(A:A, "*banana*")
(note the comma rather than a semi-colon)

Even a capital letter upper case "Banana" is found in this way

I assume that column A will be the excel column that is being looked down for a value - hence A:A .

This is likely faster and simpler than a VLOOKUP solution.

Kimberly · Answer

This is a VLOOKUP. Say your sheets are called Sheet1 and Sheet2. Your phrase is in Sheet1, A1. Your array of data is in Sheet2, A1 to B10. In Sheet1, B1 put =VLOOKUP(A1,Sheet2!A1:B10,2,FALSE) This will return the adjacent value from Sheet2 to Sheet1. WARNING: deleting any data from Sheet 2 will invalidate this formula. To "fix" the data in Sheet1 you must Copy, Paste Special, values.

Trending News

Microsoft Excel and VLOOKUP results for text?

6 Answers