Formula needed

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
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.
 
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.
It just dawned on me what you meant by that.

The formula returns 0 but you don't think it should. Is that correct?

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

If the formula returns 0 then there is a numeric 0 entry in the range. It may be that you have the display of zero values turned off.

See if this does what you want. This will return the right-most non-zero number in the range.

Book1
ABCDEFG
160154579000
Sheet1

=LOOKUP(1E100,1/Sheet1!A1:G1,Sheet1!A1:G1)

Returns 90
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
This is exactly what I was looking for thankyou thankyou thankyou and thanks to all for your great help on this.
 
Upvote 0
Here's another way:

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

Though this does require array-entry, unlike Biff's last suggestion.

Matty
 
Upvote 0

Forum statistics

Threads
1,224,578
Messages
6,179,652
Members
452,934
Latest member
mm1t1

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?

Disable AdBlock

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
Back
Top