Evaluate a range and return rightmost result (nested if?!)

[katel99]

Hi there,

I currently use an IF formula to evaluate two cells, and return the data in the 1st cell if there is nothing in the 2nd, and the 2nd cell if it is populated;

=IF(B1="",(A1),(B1))

Now I would like to do this across an array of cells, i.e. I would like the rightmost cell in a range to be the returned result. i.e.

A1 B1 C1 D1 E1 F1
10 20 15 17 17


OR


A1 B1 C1 D1 E1 F1
10 20 15 15

Apologies, not sure how to paste a screenshot - but you get the gist!

Any ideas how to do this? Is there a specific formula, or should I use an nested IF (which i am struggling with!!)

Thanks in advance

 

[steve the fish]

Try this:

=LOOKUP(9.9E+307,1:1)

 

[lrobbo314]

How about this...

=LOOKUP(9.999E+307,A1:F1,A1:F1)

 

[katel99]

Amazing, thanks both this has worked a treat!

Next question - what is the formula doing so I can try to get my head around it (and apply it again in the future!)

Thanks

 

[lrobbo314]

The lookup value, 9.999E+307, is just a huge number. The idea is that it is a number bigger than anything you could conceivably have in your lookup range. Then the Lookup function cant find that huge number, so it returns that last value in the range. And in this case, your lookup range and your result range are the same.

 

[steve the fish]

To try to put it simply no number in excel can be bigger than 9.999E+307. You often see it refered to as bignum. LOOKUP will continue to look for bignum until it exhausts its range of numbers. As we are sure it will never find it we know its just going to return the last number in the range.

 

[katel99]

Thank you both - that's really helpful