MODE Array with Criteria - #N/A Error

[RJB85]

Hi,

I am getting a #N/A error with the following formula:
{=INDEX(INDIRECT($B$25),MODE(IF(INDIRECT($B$7)=$B$2,IF((--ISTEXT(INDIRECT($B$25)))>0,MATCH(INDIRECT($B$25),INDIRECT($B$25),0)))))}

$B$25 = 'Sheet1'!$K$2:$K$1137 Text categories with three different options (R, S and K)$B$7 = 'Sheet1'!$C$2:$C$1137 Text Categories with numerous inputs (Lith1, Lith2, RM1 etc)
$B$2 = Category I want to sort by i.e. Lith1

I've used this formula on a smaller test scale and it works so I'm not sure why it doesnt work on my larger database.

The only time I get a #N/A error on the small test is when I reduce all options (R, S and K) to have a maximum of one occurrence. Not sure if this will help identify the problem!

 

[Special-K99]

If B25 = Sheet1!K2:K1137 then you're putting that RANGE in a MATCH statement. Surely you cant MATCH an entire range?

 

[RJB85]

It matches the text range against itself and then produces an array of the positions of where each category first appears.

Example:
R
R
S
S
K

Would give an array {1,1,3,3,5}

 

[Marcelo Branco]

Could you provide a small data sample (~10 rows) along with expected results?

M.

 

[RJB85]

Identified the error. It wasn't ignoring cells which had errors in the 'Sheet1'!$C$2:$C$1137. Just working on a way around that.

Not sure how to share an example as I can't upload a file and table seems pointless if you cant review the formulas.

 

[Marcelo Branco]

A data sample along with expected results is helpful to people understand your problem and for testing purposes.

To post a data sample - see section B at
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[RJB85]

Thanks Marcelo, noted for next time.

I have adjusted my formula so it now works - Calculates the MODE from a list of text category inputs, based on a criteria in an adjacent cell to the list, ensures values are text and ignores errors.
{=INDEX(INDIRECT($B$25),MODE(IF((--NOT(ISERROR(INDIRECT($B$7)))),IF(INDIRECT($B$7)=$B$2,IF((--ISTEXT(INDIRECT($B$25)))>0,MATCH(INDIRECT($B$25),INDIRECT($B$25),0))))))}

 

[Marcelo Branco]

You're welcome.

I'm glad you have found a solution to the problem, but looking at the above formula it seems to me that something simpler would be possible. I do not understand why you used INDIRECT - a volatile function that can degrade performance.

Anyway if the formula works, nothing more to say!

M.