I have a table in Google Sheets with multiple advertising channels, their spend, value generated and return on investment (ROI). I'm trying to create an excel formula where a user can put in their target ROI and I return the first row data for each channel that is equal to or more than the set ROI. So for example, using the table below, if the target is 400%, I return data for row A5 for Google, A4 for Twitter and A2 for FB.
| Channel | Spend | Value | Return |
|---|---|---|---|
| $30 | $50 | 167% | |
| FB | $40 | $100 | 250% |
| $50 | $150 | 300% | |
| $70 | $224 | 320% | |
| $80 | $320 | 400% | |
| FB | $90 | $450 | 450% |
So far, I'm using Index and Match to return each cell where the ROI condition is true. If user puts in target return in M9 for example
=INDEX(A2:A,MATCH(TRUE,INDEX(D2:D>=M9,0),0))
But this only returns the first row that matches the ROI condition irrespective of channel, I want to add a second condition for channel. That way, I can return something like
| Channel | Spend | Value | Return |
|---|---|---|---|
| $80 | $320 | 400% | |
| FB | $40 | $100 | 250% |
| $70 | $224 | 320% |
CodePudding user response：
Use query
=query(A:D,"select * where D>="&F1&" ")
