Search all instances of word and retrieve column data
On Monday, March 19, 2012 1:34:49 PM UTC-5, bwilliams1188 wrote:
I have an excel sheet where I have a list of 377 names (each with a
group number and a letter given to each name), and am trying to search
for all instances of a first name OR last name, and retrieve the row
data along with the name.
Columns A1:D1 a
A1: Last Name
B1: First Name
C1: Group Number
D1: Letter
The user will input (into a "search field") a first name, last name OR
both first and last and push the results to a table.
I have the following code which works fine if I only search for a "Last
Name" and displays all row data:
=IF(ISERROR((INDEX($A$2:$D$377,SMALL(IF($G$2=$A$2: $A$377,ROW($A$2:$A$377)-MIN(ROW($A$2:$A$377))+1,""),ROW(A1)),COLUMN(A1)))) ,"",INDEX($A$2:$D$377,SMALL(IF($G$2=$A$2:$A$377,RO W($A$2:$A$377)-MIN(ROW($A$2:$A$377))+1,""),ROW(A1)),COLUMN(A1)))
I am not sure if I can build off of this, or use a different set of
functions. I have attached an example. Thank you in advanced.
+-------------------------------------------------------------------+
|Filename: example.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=309|
+-------------------------------------------------------------------+
--
bwilliams1188
I would use a macro using FIND to locate the last name and then see if the first name agrees with the last name. If so, do whatever.
|