CODEWITHSUNDEEP
excelexcel-formulamatchreverse
Joshua Goodman
Joshua Goodman

Reputation: 13

Reverse Match Search in Excel

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.

Upvotes: 1

Views: 10340

Answers (3)

Scott Craner
Scott Craner

Reputation: 152585

This will find the last cell with a value, regardless of value:

=INDEX($A$1:$J$1,MATCH("zzz",A2:J2))

enter image description here

If you want to find the last X, regardless of what is or is not in any of the other cells, then use this formula:

=INDEX($A$1:$J$1,AGGREGATE(14,6,COLUMN(A2:J2)/(A2:J2="X"),1))

One note: this is an array type formula and will be slower than the prior formula. If you only have one it will not make a difference. If you have hundreds you will see a difference.

But if you have other text strings after the X and you want the X then it is the way to go.

enter image description here

Upvotes: 4

Eilthalearin Kheru
Eilthalearin Kheru

Reputation: 19

=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.

Upvotes: -1

PeterH
PeterH

Reputation: 1040

try out

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

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

Upvotes: 1

Related Questions