I really need help urgently.
I am working on a project that I need to show my boss tomorrow and can't seem to get a working formula. Actually, after spending a few days on this, I'm basically almost there. It's just this last final part that I still can't seem to figure out and time is clicking.
This is my first time using this forum so I am going to try and share fake data that resembles the data I am dealing with.
<colgroup><col><col><col></colgroup><tbody>
</tbody>
And then a second sheet has the following data:
<colgroup><col><col><col span="8"></colgroup><tbody>
</tbody>
For the "Sign Up Rate" column on the first row, I took care of it and got
=VLOOKUP($A$2:$A$10,Sheet2!$A$3:$B$11,2,FALSE)
as my formula and it works. It checks the ID of the first sheet against the ID of the second sheet and returns the corresponding sign up rate value. Why not just copy and paste the entire sign-up rate rate from Sheet 2 to Sheet 1? Only SOME of the IDs have a Sign Up Rate so I need to check it against the ID.
That was the easier of the two I needed to figure out though. The second one is really troubling me. For the Final Win Rate, I need to bring in the smallest percentage from numerous data entries in a single row - after matching it against the ID. The way I currently have it set up, the formula
=IFERROR(IF(MATCH(A2,Sheet2!A3:A9,0)>0,MIN(Sheet2!C3:J3),""),FALSE)
checks that the two ID's match, and if they do, it returns the minimum from the row. However, this row isn't the corresponding row but the row that is next in line. I don't know how else to explain it. As you can see, the IDs of 101 and 102 are there and return the minimum rate (aka the Final Rate) from the corresponding IDs on the second sheet. However, since there is no 103 ID, the program doesn't match it and everything goes haywire.
I hope I'm making sense. Please advise!
I am working on a project that I need to show my boss tomorrow and can't seem to get a working formula. Actually, after spending a few days on this, I'm basically almost there. It's just this last final part that I still can't seem to figure out and time is clicking.
This is my first time using this forum so I am going to try and share fake data that resembles the data I am dealing with.
| ID | Sign Up Rate | Final Win Rate |
| 101 | 90% | 0.34 |
| 102 | 55% | 0.32 |
| 103 | #N/A | FALSE |
| 104 | 61% | FALSE |
| 105 | 85% | FALSE |
| 106 | 92% | FALSE |
| 107 | #N/A | FALSE |
| 108 | 99% | FALSE |
| 109 | 53% | FALSE |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
And then a second sheet has the following data:
| ID | Sign Up Rate | Win Rate by Month | |||||||
| M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | ||
| 101 | 90% | 50% | 46% | 34% | |||||
| 102 | 55% | 40% | 39% | 35% | 32% | ||||
| 104 | 61% | 59% | 35% | 31% | 21% | 20% | |||
| 105 | 85% | 80% | 75% | 57% | 53% | 42% | 41% | 39% | 31% |
| 106 | 92% | 91% | 85% | 45% | 31% | 26% | |||
| 108 | 99% | 94% | 79% | 67% | 65% | 54% | 51% | 45% | |
| 109 | 53% | 34% | 23% | 21% | 19% |
<colgroup><col><col><col span="8"></colgroup><tbody>
</tbody>
For the "Sign Up Rate" column on the first row, I took care of it and got
=VLOOKUP($A$2:$A$10,Sheet2!$A$3:$B$11,2,FALSE)
as my formula and it works. It checks the ID of the first sheet against the ID of the second sheet and returns the corresponding sign up rate value. Why not just copy and paste the entire sign-up rate rate from Sheet 2 to Sheet 1? Only SOME of the IDs have a Sign Up Rate so I need to check it against the ID.
That was the easier of the two I needed to figure out though. The second one is really troubling me. For the Final Win Rate, I need to bring in the smallest percentage from numerous data entries in a single row - after matching it against the ID. The way I currently have it set up, the formula
=IFERROR(IF(MATCH(A2,Sheet2!A3:A9,0)>0,MIN(Sheet2!C3:J3),""),FALSE)
checks that the two ID's match, and if they do, it returns the minimum from the row. However, this row isn't the corresponding row but the row that is next in line. I don't know how else to explain it. As you can see, the IDs of 101 and 102 are there and return the minimum rate (aka the Final Rate) from the corresponding IDs on the second sheet. However, since there is no 103 ID, the program doesn't match it and everything goes haywire.
I hope I'm making sense. Please advise!
