Reverse or end value for Index/Match formula

[philobr]

Hi there,
Hoping someone can help, if my question makes sense.

I have a formula (in B4) to look up the first value which appears in a row and give me a date from the matching column (apologies I dont know how to add formula and screenshots)

=IFERROR(INDEX($D$3:$L$3,1,MATCH(A4,D4:L4,0)),"")

A4 has X
Row 4 has X at D4, and that will bring back the value from D3
A B C D E
Mon Tues
X Mon X X

What I need is a similar formula in C4 to bring back the last value so C4 will say Tues.

Any ideas?
Thanks

 

[Marcelo Branco]

Try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Search​

[/TD]
[TD]

First​

[/TD]
[TD]

Last​

[/TD]
[TD]

Mon​

[/TD]
[TD]

Tue​

[/TD]
[TD]

Wed​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

X​

[/TD]
[TD]

Mon​

[/TD]
[TD]

Wed​

[/TD]
[TD]

X​

[/TD]
[TD][/TD]
[TD]

X​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B4
=INDEX($D$3:$L$3,MATCH(A4,D4:L4,0))

Formula in C4
=LOOKUP(2,1/(D4:L4=A4),$D$3:$L$3)

M.

 

[philobr]

Perfect, thats exactly what I was looking for Marcelo, thanks so much.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.