VLOOKUP - multiple value search & sum, wildcard & su

anc

New Member
Joined
Oct 11, 2006
Messages
3
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top