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

#### 69liz

##### Board Regular
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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
IS the range in the same column/row or non consecutive.Are the values numbers or/and letters?

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

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)))

Thank you so much - that is perfect

Thank you so much - that is perfect

You are welcome.
Thanks for feedback.

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.

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

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

Replies
0
Views
1K
Replies
1
Views
66
Replies
3
Views
833
Replies
2
Views
116
Replies
4
Views
183

### Forum statistics

1,196,448
Messages
6,015,324
Members
441,889
Latest member
balolaptopgiaolong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back