Google Answers Logo
View Question
 
Q: Microsoft Excel and VLOOKUP results for text? ( Answered,   0 Comments )
Question  
Subject: Microsoft Excel and VLOOKUP results for text?
Category: Computers > Programming
Asked by: petercrowe-ga
List Price: $5.00
Posted: 17 May 2006 01:02 PDT
Expires: 16 Jun 2006 01:02 PDT
Question ID: 729655
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
Answer  
Subject: Re: Microsoft Excel and VLOOKUP results for text?
Answered By: maniac-ga on 17 May 2006 08:01 PDT
 
Hello Petercrowe,

Perhaps the best solution for this type of problem is the use of
"array formulas" which repeat a calculation over a range of cells and
can return a single results.

For example, assuming the following layout

  Cells A2 through A6 have the names of the fruit as indicated in your question.
  Cells B2 through B6 have the numbers as indicated in your question.
  Cells D2 through D4 have the names "Apple", "Banana", and "Orange"

Then copy / paste the following function into E2:
  =SUM(IF(ISERROR(SEARCH(D2,A$2:A$6)),0,B$2:B$6))
and enter as an array function (CTRL-SHIFT-Enter, or CTRL-SHIFT-click
on the check mark). If entered properly 20 should appear as the result
and the formula bar should show
  {=SUM(IF(ISERROR(SEARCH(D2,A$2:A$6)),0,B$2:B$6))}
indicating that it is entered as an array formula (braces added automatically).

You can then select E2 through E4 and fill down to compute the values
for the other types of fruit. The results I got were 20, 160, and 40.

To explain the parts of the formula:
  - SEARCH is used to find matching strings - #VALUE is returned if no match
  - ISERROR is used to return TRUE if #VALUE is returned, FALSE otherwise
  - IF is used to return 0 if ISERROR was TRUE, the count if FALSE
  - SUM is used to add the counts (of the matching cells)
Entry as an array formula is needed to cause the formula to be
repeated for each fruit / count pair of values.

See the Excel help titled
  About array formulas and how to enter them
(the name may vary slightly by Excel version) for more information on
the capabilities of array formulas in Excel. [or search for array
formula]

Please make a clarification request if you have any difficulty
adapting / entering the formula for your use or if any part of the
answer is unclear.

Good luck with your work.
  --Maniac
Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy