Right to Left index/match

[jimbowilmore]

All~

I am looking to write a formula that takes the data from u2:dr2, find the first reference to the number 70, search right to left, and then returns the corresponding date in u1:dr1.

I have tried all kinds of things and just can't seem to get it correct. Do any of your brilliant minds have time to assist me?

Thanks!

jimbo

 

[AhoyNC]

Are you trying to find the last column that has the number 70 (right to left search)?
If so, try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

[TABLE="width: 576"]
<colgroup><col width="576"></colgroup><tbody>[TR]
   [TD="width: 576"]INDEX($U$1:$DR$1,MAX(IF($U$2:$DR$2=70,COLUMN($U$2:$DR$2)-COLUMN(U2)+1)))[/TD]
 [/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Are you trying to find the last column that has the number 70 (right to left search)?
If so, try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

[TABLE="width: 576"]
<colgroup><col width="576"></colgroup><tbody>[TR]
   [TD="width: 576"]INDEX($U$1:$DR$1,MAX(IF($U$2:$DR$2=70,COLUMN($U$2:$DR$2)-COLUMN(U2)+1)))[/TD]
 [/TR]
</tbody>[/TABLE]

I am not sure how specifying the entire row range affects the dependency tables, but this is a simpler construction for your formula (still needs to be array-entered)...

=INDEX(1:1,,MAX(IF(U1:DR1=70,COLUMN(U1:DR1))))

 

[Eric W]

This non-array entered formula may work for you:

=LOOKUP(2,1/(U2:DR2=70),U1:DR1)