searchable cell

[jimmydc716]

I want to put a value in one cell and have it display all rows from a different sheet that contains that value. for example if i have one data sheet and i type in "dave" it will search that entire sheet and display all rows associated with the word "dave"

 

[jimmydc716]

i used vlookup and it will only search the left most column what i want is in the 6th column.

 

[Aladin Akyurek]

Try to post 5 rows of your data and required output.

 

[dreid1011]

i used vlookup and it will only search the left most column what i want is in the 6th column.

You can use INDEX/MATCH to search any column for the lookup value instead of being limited to the left column. I have a workbook similar to what you are trying to do. I use sheet 1 with a lookup cell to pull over information for tax codes stored on sheet 2.

The tedious part is making sure you put the lookup formula in all the cells that you want data returned to. Then, with multiple rows, you would have to drag the formulas down to accommodate the max potential rows, with some tweaks to pull each individual row of course.

As Aladin requested though, please post some data so we can better assist you.

 

[jimmydc716]

I want to be able to search the name on the far left and display all rows that have different information associated with that name.

[TABLE="width: 1023"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]16248[/TD]
[TD]483-6329[/TD]
[TD]53885-1[/TD]
[TD]Marked face .075 undersize[/TD]
[TD]M44--- U/S OD[/TD]
[TD]213--- Rising, Jared[/TD]
[/TR]
[TR]
[TD]16249[/TD]
[TD]ED3339-CSM[/TD]
[TD]53586[/TD]
[TD]Broken 7/8-9 tap where marked[/TD]
[TD]M30--- BROKEN TAP[/TD]
[TD]213--- Rising, Jared[/TD]
[/TR]
[TR]
[TD]16253[/TD]
[TD]ED3339-CSM[/TD]
[TD]53586[/TD]
[TD]FM20 holes are out of position[/TD]
[TD]M23--- OUT OF LOCATION[/TD]
[TD]924--- Toth, Nicholas[/TD]
[/TR]
[TR]
[TD]16258[/TD]
[TD]100437505[/TD]
[TD]W53174-1-2[/TD]
[TD](2x) broken taps in m12 holes[/TD]
[TD]M30--- BROKEN TAP[/TD]
[TD]912--- Kelleher, Sean[/TD]
[/TR]
</tbody>[/TABLE]

 

[jimmydc716]

correction name on far right not left.

 

[Aladin Akyurek]

Let A:F of Sheet1 house the data.

Let A1 of Sheet2 house a name like Rising, Jared

In A2 of Sheet2 enter:

=COUNTIFS(Sheet1!$F$2:$F$400,"*"&$A1)

In A4 of Sheet2, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(ISNUMBER(SEARCH($A$1,Sheet1!$F$2:$F$400)),ROW(Sheet1!$F$2:$F$400)-ROW(Sheet1!$F$2)+1),
ROWS($A$4:A4)))

In B4 of Sheet1 just enter, copy across as far as needed, and down:

=IF($A4="","",INDEX(Sheet1!A$2:A$400,$A4))
​

<strike></strike>