Hi guys, I'm currently looking for a formula that will allow me to count the last 'X' number of instances a value appears in 2 rows and then find the corresponding value in another row.
It's best to show this with an example:
For this image I need a formula which will count the first 4 instances that the letter 'B' appears in either column A or B and get the corresponding value for column D.
For example If I wanted to count the first four instances that B appeared in either column A or B and get the corresponding number of times X appeared in column D I should get the result of 1. For Y appearing in column D it would be 2.
Thanks very much in advance.
It's best to show this with an example:

For this image I need a formula which will count the first 4 instances that the letter 'B' appears in either column A or B and get the corresponding value for column D.
For example If I wanted to count the first four instances that B appeared in either column A or B and get the corresponding number of times X appeared in column D I should get the result of 1. For Y appearing in column D it would be 2.
Thanks very much in advance.