How to get last value on row data based on criteria?

[mcmuney]

I have a data series, example shown below, what I'm looking is to extract the LAST value on the bottom row based on the criteria of the top row. For example:

If top row = A, show result Grape
If top row = B, show result Apple
If top row = C, show result Banana

* note that range will vary and column data will potentially shift

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]Apple[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]Grape[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]Grape[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]Pear[/TD]
[TD]Banana[/TD]
[/TR]
</tbody>[/TABLE]

Is there a simple formula to achieve this?

Thanks.

 

[Ron Coderre]

Not exactly sure what your actual set up is....but
with your sample data in A1:H2

This regular formula, copied across, returns the values you're looking for:

A4: =IFERROR(INDEX(A2:$H2,MATCH(0,INDEX(--(A1:$H1=A1),0),0)-1),$H$2)

These are the results:

A       A        A       B        B       B       C      C
Apple   Orange   Grape   Orange   Grape   Apple   Pear   Banana
                     
Grape   Grape    Grape   Apple    Apple   Apple   Banana Banana

Is that something you can work with?

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,1/($A$1:$H$1="A"),$A2:$H2)

or if X2 = A, X3 = B, etc. In Y2 enter and copy down:

=LOOKUP(9.99999999999999E+307,1/($A$1:$H$1=$X2),$A2:$H2)