1 A B C D E
2 Y Y Y N N
3 2 3 7 9 8
I need to look for the value in Row 3 for the last “Y” in Row 2, i.e. value 7. So how do I set the conditional formula to look for the last value of “Y” and returns a value that is below it in a range. Thanks.
Hm, well, I can think of a little process to do it:
(I’ll stick with the formatting it looks like you’ve shown, and cell A1 will contain “A”)
In Row 4, Column 1, enter:
=IF(A2=“Y”,IF(COUNTIF(B2:E2,“Y”)=0,A3,""),"")
Extend the B2:E2 reference out as far to the right as you need. The point of it is to look at all the cells to its right that might contain a Y. Then just copy that cell (A4) as far to the right as the data goes.
Finally, just =SUM all of the cells in Row 4 you’ve just dealt with, and that ought to give you the answer.
Maybe there’s a more direct way to do it, but that should work.
Same thing using AND:
=IF(AND(A2=“Y”,COUNTIF(B2:E2,“Y”)=0),A3,"")
or stick this in a cell on it’s own if the data isn’t many columns wide:
=IF(E2=“Y”,E3,IF(D2=“Y”,D3,IF(C2=“Y”,C3,IF(B2=“Y”,B3,IF(A2=“Y”,A3)))))
I found this example on the web and I tested it and it does work.
If you use the lookup function
=LOOKUP(2, 1/(A1:E1=“Y”),A2:E2)
where A1:E1 = range to search through and A2:E2 = range to get the value from.
The division is just a slick way of eliminating anything that doesn’t match what you want found. Since 1/0 is undefined (not match) and 1/1 = 1, and the value to find (2) is never in that array, it finds the last closest value.
It appears I misunderstood the explanation on further reading. It is finding the last numerical value, not the last closest value.
This assumes that you move your data down row and put this formula in A1:
=INDIRECT(ADDRESS(3,LOOKUP(2,1/(A2:X2=“Y”),COLUMN(A2:X2))))
Obviously X2 should become whatever the end of your data is.
If you PM me your email address, I can send you a sample file that works.
Just to clarify, are all the Ys and Ns grouped together? There is no case where row 2 will have an ‘N’ in one column and then a ‘Y’ in a later column, is there?
Assuming that all the Ys are in a row, I would probably count the number of Ys and then use the Index function to find the value of the cell in that row/column.
=INDEX(A2:E3,2,COUNTIF(A2:E2,“Y”))
ETA: that gives you in the array A2:E3 the value of the cell in the second row, in the column number that matches the number of "Y"s in the first row.
The formula I posted has the advantage that it doesn’t require the Y and Ns to be sorted. In either case, to return the value in the cell below the Y, you need to use the INDIRECT(ADDRESS(row, col)) construct to turn the index into an address.
Maybe it depends on what version of Excel you’re using. In 2010, the INDEX formula I posted works perfectly without using INDIRECT/ADDRESS.
eta: The help description for INDEX says “Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.”
Nope you’re absolutely right. I was conflating your formula with the lookup one posted above.
In that case, this would work as well:
=INDEX(A2:X3,2,LOOKUP(2,1/(A2:X2=“Y”),COLUMN(A2:X2)))
And still has the advantage of not requiring the sorting/grouping of Ys and Ns.