Reading from multiple previous columns, without nested if?

[gmort]

Hi all,

Thanks for contributing to this forum, it's been a huge help during this project I've been working on. Unless I'm missing something obvious, it seems that I've stumbled across a new, unanswered question. Is there a technique to create a column which chooses one cell value from multiple previous columns, with the choosing method predetermined? That's a bit convoluted.

Specifically, I'm trying to create a column which will copy prices from four previous columns, each with their own pricing methodology.

A B C D E

Where D is the column which reads from the previous columns. Every row in column A contains values, and serves as the default go-to column. Columns B, C and D contain preferred (price) values, with many gaps. Where there are gaps, column D should read from the default A column. Where unique prices appear in columns B, C or D, it should read those.

A few nested if statements can do the job, such as..
D=if(B<>"", B, if(C<>"", C, A)...but this is plain ugly.

Thank you all for any ideas!
Garth

 

[erik.van.geit]

Hi, WELCOME to the Board!

Try something like this:
=LOOKUP(9.99999999999999E+307,A2:C2)

kind regards,
Erik

EDIT: you can replace the very big number by something which suits your needs like 100000 (a number which will never be reached)