lookup problem

Ralph Gregory

Board Regular
Joined
Aug 14, 2003
Messages
125
Hi I have a data sheet with names in column B On another sheet I want the user to enter for example "reg" and a list to come up from the first sheet with all the data rows with "reg" in ie "Greg" "reginald" etc on concurrent rows of the second sheet
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
Try using MS Query.

If you understand SQL, then you can use MS Query to write a parameter query that lets you focus in on a particular name and that also allows you to select which columns will be displayed.

For example, I made a test workbook called QueryTest. On a worksheet called Data I have the following data:

Surname First_Name Age City
Smith Adam 46 Toronto
Jones Shirley 32 Los Angeles
Smith Warren 71 San Diego
Green Dallas 27 Dallas
Smith Zulu 13 Pittsburgh
Then on a second worksheet called Query, I created a query starting on row 3 by using /Data/Get External Data/New Database Query.

The SQL itself looks like this:

SELECT `Data$`.Surname, `Data$`.`First_Name`, `Data$`.City
FROM `C:\Documents and Settings\My Documents\QueryTest
WHERE (`Data$`.Surname=?)
ORDER BY `Data$`.`First Name`

By leaving Age out of the query, this column simply does not show up in the result set.

I referred the query back to the workbook itself - you can obviously choose whatever location you want to store it in.

After the query was created I right clicked on C3 of the Query and picked Parameters to state where I wanted to pick up the parameter value from.
In the dialog box I chose "Get the value from the following cell: =Query!$A$1 " and checked the box next to "Refresh automatically when cell value changes."

So, when I enter "Green" in cell A1 of Query I get the following result set:

Green

Surname First Name City
Green Dallas Dallas

And when I enter "Smith" in cell A1 I get

Smith

Surname First_Name City
Smith Adam Toronto
Smith Warren San Diego
Smith Zulu Pittsburgh

There is no need to clear out old data. Adding or removing columns is just a matter of editing the SQL query. (Right click on C3, choose Edit Query, and then click on the SQL button.)

MS Query is worth knowing about. You can also easily suck in data from MS Access databases this way
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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