Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I retrieve data based on multiple search criteria? | Excel Worksheet Functions | |||
How do I retrieve data based on multiple search criteria? | Excel Worksheet Functions | |||
Retrieve Data from Word Art data | Excel Discussion (Misc queries) | |||
need formula to search column for a word and return another word | Excel Discussion (Misc queries) | |||
Replacing instances in word with data from exel | Excel Programming |