# VLOOKUP - multiple value search & sum, wildcard & su

#### anc

##### New Member
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

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

#### ravishankar

##### Well-known Member
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.

ravi

#### patrickmuldoon99

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

Replies
6
Views
209
Replies
1
Views
97
Replies
5
Views
44
Replies
1
Views
72
Replies
3
Views
63

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.

### Which adblocker are you using?

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

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