Formula to return data from furthest row right with in column that has specific text

[SU30]

I am looking to populate data from the the cell furthest right that is identified in a column with specific text.


A2 should be populating M2 (Supplier A PN)

B2 should be populating N2 (Supplier B PN)

C2 should be populating O2 (Supplier B PN Rev)



I currently have the following, but i would like to have the 2nd part of the formula $Z$1 to just extend so whenever i add new data under each heading so i do not have to alter the formula. With the current setup, if there is no data in that column furthest right, it returns a 0.

In A2:

=INDEX(2:2,MAX(($E$1:$Z$1="Supplier A PN")*COLUMN($E$1:$Z$1)))

In B2:

=INDEX(2:2,MAX(($E$1:$Z$1="Supplier B PN")*COLUMN($E$1:$Z$1)))

In C2:

=INDEX(2:2,MAX(($E$1:$Z$1="Supplier B PN Rev")*COLUMN($E$1:$Z$1)))

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[SU30]

Sorry about that. Please see attached.

[TMS]

Then:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If you changed your headers, you could use them instead of text values. Then you could have one formula throughout.

[Phuocam]

Change the column header to: Supplier A PN Supplier B PN Supplier B PN Rev

and try this:

=IFERROR(LOOKUP(2,1/($E$1:$Z$1=A$1)/($E2:$Z2<>""),$E2:$Z2),"")

[TMS]

You can even do this, so you only have to change the Far Right column in one place:

Please Login or Register  to view this content.