Nesting in the FILTER function

[TJW17]

Hello,

I have a filter formula that references a column on a second sheet.

I'd like to be able to select a different column from a drop down box on the search sheet, and I've created a small look up table so that if you select "Description" in the drop down box on the search sheet, cell J3 in the Items page will show Items!D4:D7000, and if you select "Item" in the drop down box, J3 will show Items!C4:C7000.

=FILTER(Items!B4:G7000,ISNUMBER(SEARCH(B3,Items!D4:D7000))*(B3<>""),"No results")

When I change D4:D7000 in the Filter formula to Items!J3 , I get the #VALUE! result.

I've also tried adding in the VLOOKUP formula into the space where D4:D7000 is, but the same thing happens.

=VLOOKUP('Search Sheet'!D3,Items!I7:J12,2,FALSE)

I even tried changing the LOOKUP to an IF function based on the same small table in case the FITLER didn't like the LOOKUP

=IF('Search Sheet'!D3="Description",J9,"N/A")

Is there a way to "nest" a range referenced from another cell into the FILTER formula without resorting to VBA?

Any help would be greatly appreciated!

 

[kevin9999]

Not following you 100% but is something like this your ultimate aim?

Book1
	C	D	E	F	G	H	I	J
3	Item	Description						Column C
4	Column C	Column D						Column C
5	Column C	Column D						Column C
6	Column C	Column D						Column C
7	Column C	Column D						Column C
8	Column C	Column D						Column C
9	Column C	Column D						Column C
10	Column C	Column D						Column C
11	Column C	Column D						Column C
12	Column C	Column D						Column C
13	Column C	Column D						Column C
14	Column C	Column D						Column C
15	Column C	Column D						Column C
16	Column C	Column D						Column C
17	Column C	Column D						Column C
18	Column C	Column D						Column C
19	Column C	Column D						Column C
20	Column C	Column D						Column C
21	Column C	Column D						Column C
22	Column C	Column D						Column C
23	Column C	Column D
24
Items

Cell Formulas
Range	Formula
J3:J6999	J3	=FILTER(C4:D7000,C3:D3=Search!B2,"")&""
Dynamic array formulas.

Book1
	A	B
2	Drop Down ->	Item
3		Column C
4		Column C
5		Column C
6		Column C
7		Column C
8		Column C
9		Column C
10		Column C
11		Column C
12		Column C
13		Column C
14		Column C
15		Column C
16		Column C
17		Column C
18		Column C
19		Column C
20		Column C
21		Column C
22		Column C
23
Search

Cell Formulas
Range	Formula
B3:B6999	B3	=Items!J3#
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Items!$C$3:$D$3

 

[Eric W]

I'm not quite sure either, but if this is your Items sheet:

Book2
	A	B	C	D	E	F	G
1
2
3		List	Items	Description	Header4	Header5	Header6
4		a	abc	rst	3	15	16
5		b	def	tuv	8	18	14
6		c	abc	rst	12	19	9
7		d	def	abc	17	3	10
8		e	abc	tuv	9	2	11
9		f	def	rst	15	1	11
10		g	abc	tuv	2	3	4
11		h	def	tuv	9	20	19
12
Items

Then you can do this on your Search sheet:

Book2
	A	B	C					H	I	J	K	L	M	N
1
2		Search code
3		tuv							Description	2
4
5									List	Item	Description	Header4	Header5	Header6
6									b	def	tuv	8	18	14
7									e	abc	tuv	9	2	11
8									g	abc	tuv	2	3	4
9									h	def	tuv	9	20	19
10
Search

Cell Formulas
Range	Formula
J3	J3	=IF(I3="Item",1,2)
I6:N9	I6	=FILTER(Items!B4:G11,ISNUMBER(SEARCH(B3,INDEX(Items!C4:D11,0,J3)))*(B3<>""),"No results")
Dynamic array formulas.

[XD=c:548235]=FILTER(Items!B4:G11,ISNUMBER(SEARCH(B3,INDEX(Items!C4:D11,0,J3)))*(B3<>""),"No results")[/XD][/XR][/RANGE]

I'm assuming your drop-down is in I3, then the formula in J3 will be a 1 if the drop-down is Item, and a 2 if it's Description (or anything else). Then the FILTER formula will use the INDEX function with that value to choose the column to search.

If I understand you correctly, you could actually use the "Items!C4:C7000" text in your formula if you surround it with the INDIRECT function, but this way is better (non-volatile).

 

[TJW17]

Using the INDEX function worked, thank you so much for your help with this.

 

[Eric W]

Glad we could help.