Formula for looking up value based on number of characters

[art1884]

Hello all,

I need assistance with a formula that looks up a value in different sheet based on the number of characters in the cell. for example, I have a list on sheet 2. In sheet1 A1, I need to be able to type 6 (any number, 6 is just an example). I need the formula to look for all words in sheet2 list that contain 6 characters. lastly, if its possible to display the results in a drop down list which will preserve space. thank you all for you help.

 

[kweaver]

Are the words in Sheet2 all in column A or throughout the sheet in various columns and rows?

 

[Toadstool]

Hi Art1884,

Does this do what you ask?

art1884.xlsx
	A
1	List
2	Dog
3	Elephant
4	Dexter
5	Charlie
6	Sharon
7	Paul
Sheet2

art1884.xlsx
	A
1	6
2	Dexter
3	Sharon
4
5
6
7
Sheet1

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(INDEX(Sheet2!$A$2:$A$9999,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$9999)-ROW(Sheet2!$A$1)/(LEN(Sheet2!$A$2:$A$9999)=$A$1),ROW()-ROW($A$1))),"")

 

[kweaver]

Alternatively, maybe...assumes you're running 365 which you didn't specify.

Book1
	A	B	C
1	6		Words Sheet
2	police		criticism
3	injury		police
4	growth		argument
5	orange		problem
6	cookie		injury
7			philosophy
8			soup
9			surgery
10			heart
11			growth
12			orange
13			performance
14			lake
15			gate
16			science
17			currency
18			finding
19			baseball
20			salad
21			cookie
SearchWords

Cell Formulas
Range	Formula
A2:A6	A2	=FILTER(Words!A1:A20,A1=LEN(Words!A1:A20),"")
Dynamic array formulas.

 

[art1884]

Alternatively, maybe...assumes you're running 365 which you didn't specify.

Book1
	A	B	C
1	6		Words Sheet
2	police		criticism
3	injury		police
4	growth		argument
5	orange		problem
6	cookie		injury
7			philosophy
8			soup
9			surgery
10			heart
11			growth
12			orange
13			performance
14			lake
15			gate
16			science
17			currency
18			finding
19			baseball
20			salad
21			cookie
SearchWords

Cell Formulas
Range	Formula
A2:A6	A2	=FILTER(Words!A1:A20,A1=LEN(Words!A1:A20),"")
Dynamic array formulas.

I truly appreciate the help, I didn’t realized the difference of 365.