Just a formula help i think

[shadow12345]

Hi,

I am using a min formula to return the lowest value from two cells by vlookup.

MIN(VLOOKUP($A66,$A$5:$L$14,9,FALSE),VLOOKUP($A66,$A$5:$L$14,11,FALSE)

It works fine if both cells have a value (col 9 and Col 11) but if one is blank then it decides that mean its a zero... thus messing up my min formula.

Can anyone help, i want it to show correctly (ie if only one has a value then show that, if both have a value then show the min)

 

[T. Valko]

Hi,

I am using a min formula to return the lowest value from two cells by vlookup.

MIN(VLOOKUP($A66,$A$5:$L$14,9,FALSE),VLOOKUP($A66,$A$5:$L$14,11,FALSE)

It works fine if both cells have a value (col 9 and Col 11) but if one is blank then it decides that mean its a zero... thus messing up my min formula.

Can anyone help, i want it to show correctly (ie if only one has a value then show that, if both have a value then show the min)

Is it possible that both cells from the lookups could be empty?

What kind of numbers are you dealing with? What would be the highest value in your lookup tables?

 

[shadow12345]

yes both could be blank (but i have that part covered in my whole formula (the complete one is much longer)

The numbers will be anywhere between -10000 and 10000 (posssibly with decimals as well)

 

[T. Valko]

yes both could be blank (but i have that part covered in my whole formula (the complete one is much longer)

The numbers will be anywhere between -10000 and 10000 (posssibly with decimals as well)

In that case you might be better off putting the VLOOKUPs in separate cells and then testing those cells for the min value.

A1:

=IF(VLOOKUP($A66,$A$5:$L$14,9,0)="","",VLOOKUP($A66,$A$5:$L$14,9,0))

A2:

=IF(VLOOKUP($A66,$A$5:$L$14,11,0)="","",VLOOKUP($A66,$A$5:$L$14,11,0))

Then:

=MIN(A1:A2)

 

[asad]

I could be wrong but just an idea...... will this work?

MIN(min(VLOOKUP($A66,$A$5:$L$14,9,FALSE),0),min(VLOOKUP($A66,$A$5:$L$14,11,FALSE),0))

Asad

 

[shadow12345]

I got it... abs the number first then the rest of the formula up into more cells.

 

[T. Valko]

I got it... abs the number first then the rest of the formula up into more cells.

Good deal. Thanks for the feedback!