Search all instances of word and retrieve column data
 

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.

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.