# Last Value in a Row

#### ACNB1

##### New Member
I need to a formula that will return the last value of with a certain condition.

For example, I need a formula that will give me the value of the last "A" which is 7.

A A A A F F F F
3 5 2 7 8 4 3 2

In the past I have been able to rig "IF" and "CONCATENATE" formulas to work, but I now have an issue that is slightly more complicated than the example above.

Is there an easier way to do this?

### 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
Assuming your data is in A1:H2

{=MAX((A2:H2)*(A1:H1="A"))}

Note this is an array entry, so enter this
=MAX((A2:H2)*(A1:H1="A"))
and then press CTRL+SHIFT+ENTER instead of ENTER.

Regards,

The formula gave me the answer that I needed, but when I tried to nest it in another formula (an IF formula) it did not work. I am not very familiar with arrey formulas, can you nest them within others?

Thanks

This formula (based on your data) will give you the LAST value in row 2 associated with the 'A's in row 1:

=INDEX(A2:H2,MAX(COLUMN(A1:H1)*(A1:H1="A")))

It, too, is an array formula. Barrie's formula will give you the MAX value in row 2 associated with the 'A's, which also happens to be the last value in your example.

yes. post the formula you're trying to use & the expected results etc...

I am not very familiar with arrey formulas, can you nest them within others?
I see that PaddyD has already answered 'yes'. An example of nesting lies in my formula (above). The INDEX() function is not necessarily expecting an array formula result, but it will accept one. The array portion of my formula (similar to Barrie's) is this part:

=MAX(COLUMN(A1:H1)*(A1:H1="A"))

which when array-entered (as Ctrl+Shift+Enter) will return a column number, which in turn becomes an argument to the INDEX function.

Thanks. I think I got it to work.

ACNB1 said:
Thanks. I think I got it to work.

If the range of letters is sorted (as your sample seems to imply), you don't need to use an array formula...

=INDEX(A2:H2,MATCH("A",A1:H1))

would then suffice.

I seem to be having a problem when the leftmost column is not "A". I need to start the formula in column "W" and when I set it there, the formula doesn't seem to work. Any suggestions?

Nevermind. I got it to work, but just starting with "A".

Replies
14
Views
265
Replies
7
Views
175
Replies
9
Views
376
Replies
3
Views
98
Replies
1
Views
209

1,203,527
Messages
6,055,926
Members
444,835
Latest member

### 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.

### Which adblocker are you using?

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

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