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 Home - Answers FAQ - Terms of Service - Privacy Policy