Search all instances of word and retrieve column data

[bwilliams1188]

I have an excel sheet where I have a list of 377 names (each with
group number and a letter given to each name), and am trying to searc
for all instances of a first name OR last name, and retrieve the ro
data along with the name.

Columns A11 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 O
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 "Las
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,ROW($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 o
functions. I have attached an example. Thank you in advanced

+-------------------------------------------------------------------
|Filename: example.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=309
+-------------------------------------------------------------------

 

[Don Guillett]

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 A11 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,ROW($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|
+-------------------------------------------------------------------+

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.