=vlookup() will return a value associated with the first match found--in your
sample, it would be the xyz row.

But have you thought of keeping the data in one place and then applying
data|filter|autofilter. You'll be able to filter the data to see what you
want. And by having the data in one location, it'll make it easier to make sure
sure the correct data is updated.

But if you really want...

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Confused wrote:

i have a data table with over 190 columns and over 500 rows.
In another spreadsheet, I need to write a formula that will extract data
from the data table based on a criteria which i will set. So for example, I
only want to extract the row from the data table that has the name
Residential. How do I do this?

Example: Data table
A B C D
XYZ Residential 111 Street 10,000
ABC Office 99th street 20,000
GHI Mall 10th street 40,000
JJJ Residential 14th street 10,000



--

Dave Peterson