VLOOKUP - multiple value search & sum, wildcard & su

[anc]

I'm working on an excel project which extensivley uses the VLOOKUP function to import data from one spreadsheet, which consists of imported data. I have a few functions that I am trying to get VLOOKUP to do but struggling to get answers.

1. when using the wildcard function =VLOOKUP("*Technology*", etc... it looks up the first instance it comes across, which in this case is Biotechnology and not the data I am looking for. I can't use find an exact match due to the fomatting of the imported data. is there antoher way i can do a more accurate search?

2. I also need to lookup an item which appears more than once in the data range. Again this will pick up the first found occurance but ideally i would want it to search and find all items, say in column A and sum the related numerical data in column B.

3. I also need to be able to lookup an item on the spreadsheet which would identify the end of a data range and then sum the number in a particular column in that range. Ideally i would also want the formula to be able to identify a specified starting point ie. look up "cash" as the starting row, lookup "bonds" as the end row of the range and sum a column in that range.

quite a few questions i know, but hopefully someone might be able to help.

Anc

 

[ravishankar]

I have solved this problem using search function.
In cell A1 i type search string "technology"
to search this in column B
i type formula in column C as follows
=search(a1,b1,1)
Drag this formula as many number of rows as you want.
it searches the word technology in the text contained in cell b1 from letter 1 and reports the number at which it starts for ex: if biotechnology is there technology starts at 4th letter. so output will be 4
I am able to automate it with macros. if you are familiar with VBA you can do it. or one of us will help you.
At the bottom of column c you can put
=count(c1:c10) this will tell you how many times word technology has appeared (it just counts cells having numbers)
some of your questions are unclear.
May be if you rephrase your questions we may help you

ravi

 

[patrickmuldoon99]

Question 2, you need to use a SUMIF formula to achieve your goal. Plenty about it in the XL help files