I am looking to populate data from the the cell furthest right that is identified in a column with specific text. Formula for pulling the data for the row is =LOOKUP(2,1/(F2:P2<>""),F2:P2).
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 bolded just extend so whenever i add new data under each heading, 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)))
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 bolded just extend so whenever i add new data under each heading, 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)))