Last Value in a Row

[ACNB1]

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?

 

[Barrie Davidson]

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,

 

[ACNB1]

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

 

[rrdonutz]

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.

 

[PaddyD]

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

 

[rrdonutz]

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.

 

[ACNB1]

Thanks. I think I got it to work.

 

[Aladin Akyurek]

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.

 

[ACNB1]

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?

 

[ACNB1]

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