Non array formula to get the last value

[snjpverma]

A non-array formula to get the last number in Row 1 after "YES" in Row 2. In the first table , the answer is 2. In the second table, the answer is 0 as there is no value after "Yes"

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]10
[/TD]
[TD]2
[/TD]
[TD]20
[/TD]
[TD]30
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]10
[/TD]
[TD]2
[/TD]
[TD]20
[/TD]
[TD]30
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Maybe:

	A	B	C	D	E
yes
yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=IFERROR(LOOKUP(9E+307,OFFSET(A1:P1,,LOOKUP(2,1/(A2:P2="yes"),COLUMN(A2:P2)))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A6[/TH]
[TD="align: left"]=IFERROR(LOOKUP(9E+307,OFFSET(A4:P4,,LOOKUP(2,1/(A5:P5="yes"),COLUMN(A5:P5)))),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This does not require the CSE, but it really is an array formula, since it examines multiple cells. I don't believe you can accomplish your goal without some kind of array processing.

 

[snjpverma]

oops. I am so sorry but I forgot to mention that I want the last non-zero number after "yes".

Eric, your formula works great. So, please incorporate the above condition in it to make it work for me.

Thanks for your help.

 

[Eric W]

Try:

=IFERROR(LOOKUP(9E+307,1/(1/OFFSET(A1:P1,,LOOKUP(2,1/(A2:P2="yes"),COLUMN(A2:P2))))),0)

 

[snjpverma]

Thanks Eric, that's worked exactly as needed

 

[Aladin Akyurek]

=IFERROR(LOOKUP(9.99999999999999E+307,INDEX($A$1:$J$1,MATCH("yes",A2:J2,0)+1):$J$1),0)

By the way, I don't understand Eric's need to create yet another personalized big number.

 

[Eric W]

snjpverma: Glad to help!

Aladin: While I understand your preference for typing out the whole Bignum, I don't necessarily agree with it. My formula is 15 characters shorter without the decimals, and if those extra decimals will actually make a difference on the OP's workbook, then Excel is probably not the right tool. If they do matter, then 2^1023*1.9 would be preferable, because it's even bigger than Bignum. The other reason to use Bignum is as an eyecatcher - experienced Excel users recognize it and its purpose. But even there, I think 9E+307 is just as noticeable.

Also, I think you missed the additional requirement from post 3.

 

[Aladin Akyurek]

It's not a question whether it's an eyecatcher. Also it's not a question of preference, in the usual meaning of preference. Using this Excel limit, 9.99999999999999E+307, which can be manually entered in a spreadsheet cell, we avoid all personal creations.

 

[snjpverma]

thanks for your reply Aladin.
While Eric's formula worked for me. Before Eric gave his solution, I was trying to come up with the formula myself. And I was trying the INDEX function approach like you did because OFFSET function didn't occur to my mind.
Having said that, your formula didn't suit my need as there may be multiple "yes" in the same row. And in such cases, I would like to get the last non-zero number after the last "yes".

As a part of learning, I tried making the changes in your formula but it didn't work.
Here is my try on the formula you provided.

=IFERROR(LOOKUP(9^9,INDEX($A$1:$J$1,MATCH(2,1/(A2:J2="yes"),1)+1):$J$1),0)

This works but only after confirming it with Ctrl+Shift+Enter, which I am trying to avoid because the other users are not aware of this key stroke.

So could you please suggest how I could have written your formula that it works without Ctrl+Shift+Enter.

P.S. I have changed the BigNum because the numbers I use are very small.

 

[Aladin Akyurek]

1. Your personal big number creation or your adoption of such, i.e. 9^9, proves precisely my point.

2. If you insist:

=IFERROR(LOOKUP(9.99999999999999E+307,INDEX($A$1:$J$1,LOOKUP(9.99999999999999E+307,1/(A2:J2="yes"),COLUMN($A$1:$J$1)-COLUMN($A$1)+1)+1):$J$1),0)