How can I compare 2 values in a column by determining their index from a different column?
How can I compare 2 values in a column by determining their index from a different column?
E.g. in the example below, 1,000, 1,300 and 1,100 were accepted salaries.
1,300 is greater than the immediately-previous accepted salary of 1,000.
1,100 is also an accepted salary, but it is lesser than the immediately-previous accepted salary of 1,300.
The rows containing 900 and 800 are excluded from the comparison.
The solution should not use any deleting or hiding of rows. All rows should be visible.
| Salary | Accepted | Greater than Previous Accepted |
|---|---|---|
| 1,000 | Yes | No |
| 900 | No | No |
| 1,300 | Yes | Yes |
| 800 | No | No |
| 1,100 | Yes | No |
| 700 | No | No |
| 600 | No | No |
| 1500 | Yes | Yes |
| 1000 | Yes | No |
| 1100 | Yes | Yes |
We can use LOOKUP to return the number of the last row that has a Yes before the current row and compare the numbers.
=IF(AND(B2="yes",IFERROR(LOOKUP(2,1/($B$1:B1="Yes"),$A$1:A1),1E+99)<A2),"Yes","No")
