Chris Chandler
New Member
- Joined
- Dec 13, 2013
- Messages
- 4
Greetings!
I have a conundrum regarding a bit of data validation that I'm developing. The purpose is to determine if, on a particular row of data, a specific text string exists. The text string could be found in any column past the first. For example:
<tbody>
</tbody>
The variables are selected from drop-down lists, and can be found at any point along the row. What I need to determine is when a particular Name has a particular variable in his range. I have one cell that holds the name to be found, and another that holds the variable to be found. If that name's row has the variable in question, I'd like to be able to identify it. For example, how can I determine if Eric has a pamphlet associated within his variables?
I've tried the Index-Match-Match method, and I can't get the column to be determined by the specific row I need. I can easily find Eric's row with Match:
=MATCH(K1,A:A,0)
Where K1 is the cell that holds the name for which I'm looking. This would return "5", which is just what I want. This cell changes value, depending on criteria elsewhere in the workbook.
Now, my problem is that I can't figure out how to use a similar MATCH clause, referencing row 5 (which can easily change to another row) for the lookup column, because (by design), the column can't be predicted, and so the header is of no use, because any one of the nine headers could be one of several variables. I would like to point out that a particular row would only have a particular variable once. Eric wouldn't ever see "Pamphlet" thrice, for example, only the one time.
Note that this is a simplified distillation of my sheet, which has several hundred rows (all with distinct names) and dozens of variables across 20 columns. Please don't get caught up in the example, but rather on how to determine, "Yes,the variable for which you are looking is indeed within the row range of the Name you selected."
Thanks so much for your consideration!
-Chris
I have a conundrum regarding a bit of data validation that I'm developing. The purpose is to determine if, on a particular row of data, a specific text string exists. The text string could be found in any column past the first. For example:
| Name | variable 1 | variable 2 | variable 3 | variable 4 | variable 5 | variable 6 | variable 7 | variable 8 | variable 9 |
| Mike | coupon | ||||||||
| Joe | discount | coupon | refund | ||||||
| George | refund | ||||||||
| Eric | coupon | refund | pamphlet | media | poster | online | |||
| Harold | online | ||||||||
| John | media | poster | |||||||
| Ken | friend | online | coupon | ||||||
| Adam | media | ||||||||
| Bob | refund | pamphlet |
<tbody>
</tbody>
The variables are selected from drop-down lists, and can be found at any point along the row. What I need to determine is when a particular Name has a particular variable in his range. I have one cell that holds the name to be found, and another that holds the variable to be found. If that name's row has the variable in question, I'd like to be able to identify it. For example, how can I determine if Eric has a pamphlet associated within his variables?
I've tried the Index-Match-Match method, and I can't get the column to be determined by the specific row I need. I can easily find Eric's row with Match:
=MATCH(K1,A:A,0)
Where K1 is the cell that holds the name for which I'm looking. This would return "5", which is just what I want. This cell changes value, depending on criteria elsewhere in the workbook.
Now, my problem is that I can't figure out how to use a similar MATCH clause, referencing row 5 (which can easily change to another row) for the lookup column, because (by design), the column can't be predicted, and so the header is of no use, because any one of the nine headers could be one of several variables. I would like to point out that a particular row would only have a particular variable once. Eric wouldn't ever see "Pamphlet" thrice, for example, only the one time.
Note that this is a simplified distillation of my sheet, which has several hundred rows (all with distinct names) and dozens of variables across 20 columns. Please don't get caught up in the example, but rather on how to determine, "Yes,the variable for which you are looking is indeed within the row range of the Name you selected."
Thanks so much for your consideration!
-Chris
