Formula needed

[baggarwal]

Hi All:

I have 7 columns (Columns A to Columns G) and the order of importance is that if there is a value in columns A and B I need a formula that will take the value in B. If there is a value in column B and Column C I need the value in Column C. The values may not be in all columns but the formula that I need should always put in the value in the last column out of the 7 columns in the spreadsheet. Can someone please suggest a formula.

Here is an example
1 2 3 4 5 6 7 Desired Result
5 6 6
4 2 2
3 7 7
4 5 5

Can someone please suggest a formula that can do this. The formula should be column driven as it does not matter what value is in there as long as it looks for the value in the right most column.

 

[Matty]

Try:

=LOOKUP(9.99999999999999E+307,A1:G1)

Matty

 

[baggarwal]

Hi Matty, I think because of the formatting of the text the formula is not working. Can you propose another one.

 

[baggarwal]

I am taking values from another sheet so the values are actually links from another sheet. How can I adapt the formula to get what I want?

 

[T. Valko]

Hi Matty, I think because of the formatting of the text the formula is not working. Can you propose another one.

Formatting of the text? What text? You said the cells contain numbers?

Matty's suggestion should do what you want, return the right-most numeric value in the range.

=LOOKUP(9.99999999999999E+307,A1:G1)

Although, I would write it like this:

=LOOKUP(1E100,A1:G1)

BOTH FORMULAS WILL RETURN THE EXACT SAME RESULT.

 

[T. Valko]

I am taking values from another sheet so the values are actually links from another sheet. How can I adapt the formula to get what I want?

Just include the sheet name:

=LOOKUP(1E100,Sheet2!A1:G1)

 

[Aladin Akyurek]

I am taking values from another sheet so the values are actually links from another sheet. How can I adapt the formula to get what I want?

Prefix the range with the sheet name like in Sheet1!A1:G1. The formula picks out the last numeric value from the reference (range) it's given... see:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278

 

[baggarwal]

Hi All for some reason the values are showing zero. If I change the column reference from A:G to C:I would that cause any problems. Is there any other formula that would do the same thing.

 

[Aladin Akyurek]

Hi All for some reason the values are showing zero. If I change the column reference from A:G to C:I would that cause any problems. Is there any other formula that would do the same thing.

Do you mean (1) columns from C to I or (2) something like C1:I1?

If (2) and there are numbers in C1:I1...

=LOOKUP(9.99999999999999E+307,C1:I1)

will return the last number from C1:I1 even if that's a real 0.

 

[T. Valko]

Hi All for some reason the values are showing zero. If I change the column reference from A:G to C:I would that cause any problems. Is there any other formula that would do the same thing.

Are you sure you have numbers in the range of interest?

If the range of interest is A1:G1, what result do you get from this formula:

=COUNT(A1:G1)

This formula will return the right-most entry whether it's text or numeric:

=LOOKUP(2,1/(A1:G1<>""),A1:G1)