Search a range and return a value from a non-blank cell

[69liz]

Hi,

I have a range of 5 cells - only one of them shows a value at any one time because they are dependent on an input in another part of the sheet.

I now need to record the value elsewhere in the sheet. How can I search the range of 5 and return the value that is showing?

All of the 5 contain an IF statement but only 1 will ever show a value at a time.

Thanks,

Liz

 

[Robert Mika]

IS the range in the same column/row or non consecutive.Are the values numbers or/and letters?

 

[martindwilson]

also what is in the cells that are not filled is it ""

 

[69liz]

Yes, they are in the same column. Consecutive. D27:D29. And yes, "" is what is in the cells that are not filled.
Thanks

 

[Robert Mika]

Try:
=INDEX(D27:D29,MATCH(TRUE,INDEX(D27:D29<>"",,0)))

 

[69liz]

Thank you so much - that is perfect

 

[Robert Mika]

Thank you so much - that is perfect

You are welcome.
Thanks for feedback.

 

[Aladin Akyurek]

Yes, they are in the same column. Consecutive. D27:D29. And yes, "" is what is in the cells that are not filled.
Thanks

Try:
=INDEX(D27:D29,MATCH(TRUE,INDEX(D27:D29<>"",,0)))

Hi,

I have a range of 5 cells - only one of them shows a value at any one time because they are dependent on an input in another part of the sheet.

I now need to record the value elsewhere in the sheet. How can I search the range of 5 and return the value that is showing?

All of the 5 contain an IF statement but only 1 will ever show a value at a time.

Thanks,

Liz

If the range is expected to house a number...

=LOOKUP(9.99999999999999E+307,Range)

If the range is expected to house a text value whose length >= 1...

=LOOKUP(9.99999999999999E+307,SEARCH("?",Range),Range)

These would be a tad faster.

 

[martindwilson]

for only 5 values
=d27&d28&d29&d30&d31 for text
=--(d27&d28&d29&d30&d31)for number

 

[69liz]

Thanks Aladin.
I came back to the thread because the original solution stopped working - would only work for the 2nd, 4th and 5th values in the range.
Your solution seems to work just fine!
Thanks