How do I get Excel to read from right to left and find the first case, where there is a "X&quot-CodePudding

How do I get Excel to read from right to left and find the first case, where there is a 'X' in the "Price adjustment?"-row, and then return the number from the yellow field (below the 'X') in the "New Price"-row / "01-07-2023"-column?

I want the 1.910 EUR to be displayed in the cell, where it now says 1.983 (as illustrated on the picture).

enter image description here

So far, I have used the following formula:

=HLOOKUP("X";$G$12:L14;3;TRUE)

and it is actually working in all the columns before the '01-07-2023'-column.

CodePudding user response：

You could try:

Formula in H2:

=LOOKUP(2,1/(A1:F1="X"),A3:F3)

ms365 Users could alternatively also use XLOOKUP():

=XLOOKUP("X",A1:F1,A3:F3,,0,-1)

And BETA-channel users could even try:

=TAKE(FILTER(A3:F3,A1:F1="X"),,-1)