Return Value in Range of Cells

[laurayas]

Hello,

I am creating a fairly complex spreadsheet which is formulated all over however I am stuck when it comes to a specific formula can someone help?

I have a range of cells that will populate with Text depending on the information processed previously in the sheet, only one cell within a row will be populated at one time. The cells to populate are O5:W5 and depending on the text within a cell within this range I need the text to return into cell X5. There are only 3 different text strings that will populate within the cells and then X5 is conditionally formatted to colour depending on its contents... example below:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Own car
[/TD]
[TD]Own car Paid
[/TD]
[TD]Own car Outstanding
[/TD]
[TD]Other - not to pay
[/TD]
[TD]Other Paid
[/TD]
[TD]Other Outstanding
[/TD]
[TD]Unknown not to pay
[/TD]
[TD]Unknown Paid
[/TD]
[TD]Unknown Outstanding
[/TD]
[TD]Permit Obtained
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PAID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PAID[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NO ACTION REQUIRED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NO ACTION REQUIRED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OUTSTANDING[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OUTSTANDING[/TD]
[/TR]
</tbody>[/TABLE]


At the moment I can get the sheet to return the value into O5 if its populated and nothing if its empty with "=IFERROR(INDEX(O15:W15,MATCH("*",O15:W15,0)),"")" however as I said it will only return the value in O5 and nothing else within the range.

could someone advise a formula that will help me with this problem?

thanks

 

[pgc01]

Hi
Welcome to the board

Try:

=IFERROR(LOOKUP(2,1/(O15:W15<>""),O15:W15),"")

 

[laurayas]

Hi
Welcome to the board

Try:

=IFERROR(LOOKUP(2,1/(O15:W15<>""),O15:W15),"")

Hi, thanks for your response. I have tried the formula however it is returning nothing. Blank Cell. any other suggestions?

thanks

 

[billszysz]

LOOKUP is enough

=IFERROR(LOOKUP("ZZZZZZ",O15:W15),"")

or
You have to change your formula by replacing "*" to "?*"

=IFERROR(INDEX(O15:W15,MATCH("?*",O15:W15,0)),"")

 

[laurayas]

Hi billszysz,

I Have tried both, the 1st suggestion won't work as there is not one specific text string that im looking for, there are 3 possibilities it could be. Suggestion 2 is returning the value of cell 015 if it is populated however if the text string is in Q15 for example then its returned nothing.

thanks

 

[billszysz]

So, it means, I don't understand your problem. (my english is poor)

 

[pgc01]

Hi, thanks for your response. I have tried the formula however it is returning nothing. Blank Cell. any other suggestions?

thanks

Hi

Works for me.

Are you sure you have something in O15:W15?

 

[pjmorris]

Hi, I think pgc01's answer was right except that the cell references were out. It worked for me with this formula placed in Cell X2: =IFERROR(LOOKUP(2,1/(O2:W2<>""),O2:W2),""). Note the difference range references.

(I tested it by simply copying the sample data starting at cell O1 (which is the heading row).

Hope this helps.

Regards

PS. I don't actually know how the formula works so am now off to investigate!

 

[laurayas]

Hi, I think pgc01's answer was right except that the cell references were out. It worked for me with this formula placed in Cell X2: =IFERROR(LOOKUP(2,1/(O2:W2<>""),O2:W2),""). Note the difference range references.

(I tested it by simply copying the sample data starting at cell O1 (which is the heading row).

Hope this helps.

Regards

PS. I don't actually know how the formula works so am now off to investigate!

Hi All,

thanks for your suggestions, i couldnt get them to work so went about it another way. Rather than returning text into the different sections I returned a value and then depending on the value it returned the text "Paid","Outstanding","No action Required".

Seems to work ok and have put the sheet in play with the business

thank you

 

[pjmorris]

Hi Laura, There are always multiple solutions to excel problems so delighted you've got it fixed.

Thanks for the feedback.