Reverse Match Search in Excel

Question

I need to do a match/index search in reverse right to left

each cell with have an x i need do go from right to left find what column the x is in and report the position so can then go to the top of that column and pull that data. I basically need to find out what column is the last X in.

A         B        C        D    E          F      G      H      I       J
State   27-Aug  28-Aug  29-Aug  30-Aug  31-Aug  1-Sep   2-Sep   3-Sep   4-Sep
VI       X        X       X        X       X      X     

in above example 3 rows 10 columns if i want to see that the last X is in Column G(7) then i use the index to go to that column(7), row A to 1-sep as the answer.

Answer 1

try out

=INDEX(1:1,1,COUNTIF(2:2,"X")-1) 

Assuming there are no gaps within the stream of X's

Answer 2

=MATCH(2,IF(A2:J2="x",1,FALSE))

This is an array formula. This will give you last position of x. Then, like you indicated, put the result inside INDEX function and you should be good. I like this option because i can put any condition inside the if statement, like >0.