Index - Match isn't enough. How do I...

[CatyH]

Hi all! Here's my dilemma:

I have a huge array (here represented by columns with headers 1a,1b,1c, and 1d. Each row may or may not have a value in it for the corresponding column (these are indices and only increasing - basically flags to detect the first instance of 1a is a Deer, the second instance of 1a is a Fox, and so on).

I'm trying to program a way that can look up :
If cell is 1a then find the first instance (1) and return its animal (Deer)
If cell is 1a then find the second instance (2) and return its animal (Fox)
If cell is 1a then find the third instance of (3) and return its animal (not found so returns null). etc etc

If cell is 1b then find the first instance (1) and return its animal (Elephant) etc etc..

the problem is that "cell" is a variable. So I won't always know which column (1a, 1b, 1c, etc) to use as the index/match reference.

Currently, the only way I can conceive of this is to create the Index with Match and have nested IF statements, one for 1a, one for 1b, etc... super-duper clunky... considering it's not just 1a-1d but actually 1a-1z!

Is there an index within an index or a an index within a match... or a match within a match that can do this?

I'm not averse to VBA, but if the solution is possible without it, I'd like to try that. THANK YOU!

	1a	1b	1c	1d
Deer	1		1
Elephant		1
Fox	2	2
Goat		3	1

 

[CatyH]

Here's my clunky code:

=IFERROR(INDEX($A$2:$A$7,IF($G$2=$B$1,MATCH($G$3,$B$2:$B$7,0),IF($G$2=$C$1,MATCH($G$3,$C$2:$C$7,0),IF($G$2=$D$1,MATCH($G$3,$D$2:$D$7,0),IF($G$2=$E$1,MATCH($G$3,$E$2:$E$7,0),"")))),1),"")

Hi all! Here's my dilemma:

I have a huge array (here represented by columns with headers 1a,1b,1c, and 1d. Each row may or may not have a value in it for the corresponding column (these are indices and only increasing - basically flags to detect the first instance of 1a is a Deer, the second instance of 1a is a Fox, and so on).

I'm trying to program a way that can look up :
If cell is 1a then find the first instance (1) and return its animal (Deer)
If cell is 1a then find the second instance (2) and return its animal (Fox)
If cell is 1a then find the third instance of (3) and return its animal (not found so returns null). etc etc

If cell is 1b then find the first instance (1) and return its animal (Elephant) etc etc..

the problem is that "cell" is a variable. So I won't always know which column (1a, 1b, 1c, etc) to use as the index/match reference.

Currently, the only way I can conceive of this is to create the Index with Match and have nested IF statements, one for 1a, one for 1b, etc... super-duper clunky... considering it's not just 1a-1d but actually 1a-1z!

Is there an index within an index or a an index within a match... or a match within a match that can do this?

I'm not averse to VBA, but if the solution is possible without it, I'd like to try that. THANK YOU!

	1a	1b	1c	1d
Deer	1		1
Elephant		1
Fox	2	2
Goat		3	1

 

[Toadstool]

I'm not sure I understand the question but assuming "cell" is B1 then does this satisfy?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Cell	1b
2	From:	$F$4
3	To:	$F$9			1a	1b	1c	1d	1e	1f	1g	1h	1i	1j	1k	1l	1m	1n	1o	1p	1q	1r	1s	1t	1u	1v	1w	1x	1y	1z
4	How Many?	3		Deer	1		1				1				1
5				Elephant		1				1	2			1
6	Animal	Elephant		Fox	2	2					3	1	1		2
7		Fox		Goat		3	1				4		2
8		Goat		Mole					1		5			2	3
9				Badger					2	2	6
10
Sheet1

Cell Formulas
Range	Formula
B2	B2	=ADDRESS(ROW($D$4),MATCH($B$1,$E$3:$AD$3,0)+COLUMN($D$3))
B3	B3	=ADDRESS(COUNTA($D$4:$D$999)+ROW($D$3),MATCH($B$1,$E$3:$AD$3,0)+COLUMN($D$3))
B4	B4	=COUNT(INDIRECT(B2&":"&B3))
B6:B10	B6	=IF(ROWS($A$6:$A6)>$B$4,"",INDEX($D$4:$D$999,AGGREGATE(15,6,ROW($D$4:$D$999)-ROW($D$3)/(INDIRECT($B$2&":"&$B$3)<>""),ROWS($A$6:$A7)-1)))

 

[CatyH]

Thank you, Toadstool!

well... I guess it would have helped if I spelled out the inputs and outputs... I wanted the input to be which column (1a-1z) and the number (In your example, 1-6, though for 1a you only have either 1 or 2). The OUTPUT would be the associated animal.

So - if input is 1k and 2; output would be Fox.

I THINK I've been able to put something together using INDEX with CHOOSE, though I did have to go through an extremely tedious process of creating 1a-1z named ranges (oh... and ok... since nothing is every easy... it was actually 1a-1j,2a-2j,3a-3f,4a-4h... on each of 10 different tabs... so 340 named ranges which i created using VB), but any one formula is only referring to one of the tabs... so here's my current solution... though... I THINK I should be able to be an array of arrays?

I'm thinking this formula COULD be simplified IF I were allowed to create an array, say SDR1, that was comprised of SDR_1a, SDR_1b, etc...

?


=INDEX('State Data Request #1'!$A$2:$A$99,MATCH($X3,CHOOSE(VLOOKUP($A3,RQs!$A:$G,7,FALSE),SDR1_1a,SDR1_1b,SDR1_1c,SDR1_1d,SDR1_1e,SDR1_1f,SDR1_1g,SDR1_1h,SDR1_1i,SDR1_1j,SDR1_2a,SDR1_2b,SDR1_2c,SDR1_2d,SDR1_2e,SDR1_2f,SDR1_2g,SDR1_2h,SDR1_2i,SDR1_2j,SDR1_3a,SDR1_3b,SDR1_3c,SDR1_3d,SDR1_3e,SDR1_3f,SDR1_4a,SDR1_4b,SDR1_4c,SDR1_4d,SDR1_4e,SDR1_4f,SDR1_4g,SDR1_4h),0),1)[/QUOTE]

 

[Toadstool]

I'm sorry but I still can't follow.

 

[AlanY]

may be

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		1a	1b	1c	1d	1e	1f	1g	1h	1i	1j	1k
2	Deer	1		1				1				1
3	Elephant		1				1	2			1
4	Fox	2	2					3	1	1		2
5	Goat		3	1				4		2
6	Mole					1		5			2	3
7	Badger					2	2	6
8
9
10	Input	1i	2
11	Output	Goat
12
Sheet2

Cell Formulas
Range	Formula
B11	B11	=INDEX(A2:A7,MATCH(C10,OFFSET($A$1,1,MATCH(B10,B1:L1,0),6,1),0))

 

[CatyH]

AlanY!!!! THIS IS IT!!!!!!

I wish I understood what the OFFSET was doing here... I mean I sort of get it... but never in a million years would I have put this together! HOW?! YOU ARE AMAZING!!!!!

WOOOOOOOOO!!!!! THANK YOU THANK YOU THANK YOU!!!!!

may be
...

 

[AlanY]

you're welcome

the offset() function is just one way to define the location and size of a range,

 

[CatyH]

you're welcome

the offset() function is just one way to define the location and size of a range,

I've used it in dynamic pick-lists and for dynamic printing ranges (though sadly, the offset works only the first time and doesn't stay truly dynamic in printing situations for some odd reason)... but never in a million years did i put this together... GENIUS! seriously! I just implemented the code on each of the 10 reference tabs and my main summary sheet is DONE in a fraction of the time it took me to set up all those unneeded NamedRanges...

such an elegant solution! I did have to modify it a bit adding 8 to the Offset part because the index (col A) was not actually flush with the codes. There were 8 rows in between where users are picking the codes from a series of dropdowns. My grid of codes is the intermediate step to index the occurrence of each code for each row and count them down the page.

This was a fun project, made much more fun by this super-cool solution. You SERIOUSLY have NO IDEA how awesome this is!!!!