Selecting a value in a row of cells that is greater than zero

[Damian76]

I am trying to figure out how to enter a formula into a cell, that will look at a row of cells and select the value in a specific cell. Example:

FORUMLA CELL (N) O P Q R S T U
=(?) 0 0 0 Monday 0 0 0

So I want the formula in cell to look at the row of cells (O to U) and select cell R (Monday) since that is the only cell in that row that has a value greater than zero.

So cell N....will show "Monday"....because it is the only cell with a value greater than zero in that row of cells (O to U).

Thanks for the help!

D

 

[Damian76]

The post didn't keep the cell alignments..... Here is how the rows and cells would line up:

N O P Q R S T U
= 0 0 0 m 0 0 0

m=Monday or the value greater than zero in row O1 through U1.

I want Cell N to show Monday after it looks at O1 through U1 and find that Monday in cell R1 is the only value greater than zero.

 

[Peter_SSs]

Welcome to the MrExcel board!

Like this?

Book1
	N	O	P	Q	R	S	T	U
1	Monday	0	0	0	Monday	0	0	0
Not Zero

Cell Formulas
Range	Formula
N1	=LOOKUP(2,1/(O1:U1<>0),O1:U1)

Might you ever have more than one non-zero value in the row? If so, what do you want returned? (eg All, first, last?)


Note: My signature block below has a link with suggestions for showing screen shots like I have here.

 

[Damian76]

Only one of the cells will have a value greater than 0 at a given time. That formula works, but if all the cells are zero, it gives the #N/A error. I could add a conditional formatting option to make the sell not show anything if there is an error, etc....

 

[Peter_SSs]

.. if all the cells are zero, it gives the #N/A error. I could add a conditional formatting option to make the sell not show anything if there is an error, etc....

.. or you could just change the formula to this:

=IFERROR(LOOKUP(2,1/(O1:U1<>0),O1:U1),"")

 

[Damian76]

That worked great....and is an easy way to get rid of the #N/A error!

One more question.....I am using the CONCATENATE function to look up the value of a cell based on two cells concatenated together...

Example
Cell 1 480 Cell 2 10 Concatenated cell is then 48010.....the lookup is for the 48010 and then the next column is the cell that I want the output.....it is working great.

BUT....my question....I am going to use THREE Concatenated cells like this:

Cell 1 (480) Cell 2 (4) Cell 3 (18.8).......this will then be 480418.8 Concatenated....and will yield say MONDAY as the output value in the next column that I want. The Cells 2 and 3 could be reversed of which I would still want MONDAY as the output...

Cell 1 (480) Cell 2 (18.8) Cell 3 (4).....this will then be 48018.84 Concatenated....and would still yield MONDAY as the output value in the next column.

So the big question, can the LOOKUP function...which would be VOOKUP(1&2&3), Table, Argument, Column.....be fashioned so the 2&3 part of the lookup could be either or to get the same output, or do I just need to add additional vertical columns to have each of the Concatenated cells exactly?

 

[Peter_SSs]

That worked great....

Good news!

I'm not certain that I have understood your new question, but I think this is what you are getting at. In pseudo-formula form it would be like:

=IFERROR(VLOOKUP(1&2&3,Table,Column,T/F),IFERROR(VLOOKUP(1&3&2,Table,Column,T/F),""))