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.

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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>