Match\Index to find all the records

**itsmejan24** · 04-30-2012, 10:31 AM

Hi. Please help me on this scenario. I would like to use MATCH\INDEX on finding all records for specific name. Example, if I enter "John" in J2, then from H3 down should show all the records of John in column B, C, and D. I hope my scenario is clear. Pleas help. Thank you in advance.

**dilipandey** · 04-30-2012, 10:42 AM

Hi itsmejan24,

This is achievable but would prefer a sample file from you side... please upload. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**itsmejan24** · 04-30-2012, 11:53 AM

Thank you for quick respond.

Sample:

A1:JOHN B1: "INFO-B" C1: "INFO-C"
A2:"MARY" B2: "FORM-A" C2 : "FORM-B"
A3:"JOHN" B3: "INFO-3B" C3: "INFO-3C"
and so on

J2: I type in JOHN
H2 = should contain INFO-B
H3 = should contain INFO-3B
I2 = should contain INFO-C
I3 = should contain INFO-3C
and so on....

Thank you.

**dilipandey** · 04-30-2012, 12:28 PM

Hi itsmejan24,

You can use the below formula:-

{=INDEX($A$1:$C$3,SMALL(IF($A$1:$A$3=$J$2,ROW($A$1:$A$3),""),ROW($A1)),2)}

Above is an array formula and need to be entered using Ctrl + Shift + Enter key combination. thanks

Match Index to find all records.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**itsmejan24** · 04-30-2012, 03:06 PM

Hi Dilipandey....Thank you very much for your time. It worked perfectly. I really appreciated your help. Thank you again. BRAVO.

**itsmejan24** · 04-30-2012, 03:09 PM

Dilipandey...Just 1 more question. Excel 2007 tends to slow when it tries to locate the records. Is it because of the array formula? or is there way to make the search faster? Thank you again.

**dilipandey** · 05-02-2012, 12:23 AM

You are welcome itsmejan24...

Number of records and usage of complex formulas could be a reason for slowness.. to overcome this, I would suggest you that after calculation is done, copy paste the formulas to values - leaving the first row with formulas.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>