tring to add a search cell


Well-known Member
Mar 6, 2013
I have a data base that I want to search where if I type Sm* it will bring up all of the last names that start with Sm. also useing the same cell I would like to be able to search lot numbers and sales order numbers. I have a data set, please tell me what or where I need to add for someone can help me with the VBA code.

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

I can help you with searching for a Partial Text Lookup...
Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records - YouTube

Sample data and example results based on the formula method in the above link....

Excel Workbook
1ID NumberNameAmountAnimalTransportDate
2AA10000Bart Simpson368catBike01/01/2011
3AA10010Lisa Simpson277dogHelicopter02/01/2011
4AA10012Grandpa Simpson474HorseBus03/01/2011
5AA10003Lisa Simpson163Mousetrain04/01/2011
6AA10004Marge Simpson330fishHelicopter05/01/2011
7AA10050Maggie Simpson346catPlane01/01/2011
8AA10006Milhouse Van Houten472PandaTut Tut07/01/2011
9AA10007Ned Flanders343TigerCoach08/01/2011
10AA10008Otto Mann300LionCarriage09/01/2011
11AA10009Rod Flanders343TigerBus10/01/2011
12AA10010Tod Flanders100cowtrike01/01/2011
13AA10012Moe Szyslak147dogCar01/01/2011
14AA10013Grandpa Sim267HorseHelicopter04/01/2011
15AA10014Lisa Sim458Mousetrain05/01/2011
16AA10015Marge Simpson436HorseTram06/01/2011
17AA10020Bart Sim496TigerBoat08/01/2011
21ID NumberNameAmountAnimalTransportDate
22AA10000Bart Simpson368catBike40544
23AA10010Lisa Simpson277dogHelicopter40545
24AA10012Grandpa Simpson474HorseBus40546
25AA10003Lisa Simpson163Mousetrain40547
26AA10004Marge Simpson330fishHelicopter40548
27AA10050Maggie Simpson346catPlane40544
28AA10013Grandpa Sim267HorseHelicopter40547
29AA10014Lisa Sim458Mousetrain40548
30AA10015Marge Simpson436HorseTram40549
31AA10020Bart Sim496TigerBoat40551

The formula used in A22 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.
You can copy the results to another sheet and the sheet reference will be added.

This formula "may" be used for what you require, eg searching for different criteria, which I assume will be based in different columns, but I haven't tested it.

I hope this helps.

Last edited:
Upvote 0
thanks for the video but I'm getting an error now can someone look and help please?

Upvote 0

Looking at your formula...

=IF(ROWS(B$7:B7)>F2,"",INDEX(ExDrexelInvoices!$D$5:$D$2000,(SMALL(IF(ISNUMBER(SEARCH($F$3,ExDrexelInvoices!$F$85:$F$2000))+(ISNUMBER(SEARCH($F $3,ExDrexelInvoices!$G$85:$G$2000))+(ISNUMBER(SEARCH($F$3,ExDrexelInvoices!$F$85:$F$2000)))),ROW(ExDrexelInvoices!$D$5:$ D$2000)-ROW(ExDrexelInvoices!$D$5)+1),ROWS(B$7:B7)))))

Does this ExDrexelInvoices!$D$5:$D$2000 need to be ExDrexelInvoices!$D$85:$D$2000
Or this ExDrexelInvoices!$F$85:$F$2000 to this ExDrexelInvoices!$F$5:$F$2000
The F2 needs to be "locked" $F$2
$F $3 should be $F$3

Upvote 0
I was useing the "+" as an or statement will this work?
Upvote 0
this is curently what I'm useing tring to get rid of the choose function and get search function in there
Upvote 0

Forum statistics

Latest member

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
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 "".
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