MIN(INDEX(( 'Not working

[profector]

I've been using a formula like the one below to find the MAX Value in the next column that matrches what I type in A1.

Now when I try the same formula exchanging Max for Min I get nothing, no error messges nothing. Even though I know what type in A1 exist in DATA!$B$2:$B$65536 several time and has a related value in the next column.

A1 and DATA!$B$2:$B$65536 are strings or null and DATA!$C$2:$C$65536 are numbers or null

Any ideas?

=MAX(INDEX((DATA!$B$2:$B$65536=A1)*(DATA!$C$2:$C$65536), FALSE))

[profector]

NM I answered my own question. Kinda different approach but it still works.


=MIN(IF(DATA!$B$2:$B$65536=A1,IF(DATA!$C$2:$BC$65536>0,OpenPO!$C$2:$C$65536)))

[daddylonglegs]

The problem is that you are using * which means that your formula returns zero when A1 doesn't match. That's fine for MAX but MIN will always be zero.

You say that some cells in column C are "Null", what does that mean? Are these truly blank, or zero, or a formula that returns ""?

You can probably use a formula like

=MIN(IF(data!$B$2:$B$65536=A1,IF(data!$C$2:$C$65536,data!$C$2:$C$65536)))

confirmed with CTRL+SHIFT+ENTER

That should give you the lowest value that isn't zero.....or do you need to include zeroes?

[vane0326]

The problem is that you are using * which means that your formula returns zero when A1 doesn't match. That's fine for MAX but MIN will always be zero.

Hi profector & daddylonglegs,

I think this might work.

=MIN(INDEX((10^5-(A2:A5=D2)*(10^5-B2:B5)),0))