CatyH
Board Regular
- Joined
- Jun 27, 2017
- Messages
- 84
- Office Version
- 365
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!
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 |