Question About Lookup when Both Column and Row are Variables

[Chris Chandler]

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]variable 1[/TD]
[TD]variable 2[/TD]
[TD]variable 3[/TD]
[TD]variable 4[/TD]
[TD]variable 5[/TD]
[TD]variable 6[/TD]
[TD]variable 7[/TD]
[TD]variable 8[/TD]
[TD]variable 9[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]coupon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]discount[/TD]
[TD]coupon[/TD]
[TD]refund[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]refund[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD]coupon[/TD]
[TD]refund[/TD]
[TD]pamphlet[/TD]
[TD]media[/TD]
[TD]poster[/TD]
[TD]online[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harold[/TD]
[TD]online[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]media[/TD]
[TD]poster[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ken[/TD]
[TD]friend[/TD]
[TD]online[/TD]
[TD]coupon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]media[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]refund[/TD]
[TD]pamphlet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

 

[FormR]

Something like this maybe:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	variable 1	variable 2	variable 3	variable 4	variable 5	variable 6	variable 7	variable 8	variable 9		Name	Joe
2	Mike	coupon										Variable	Coupon
3	Joe	discount	coupon	refund								Formula	Yes variable exists
4	George	refund
5	Eric	coupon	refund	pamphlet	media	poster	online
6	Harold	online
7	John	media	poster
8	Ken	friend	online	coupon
9	Adam	media
10	Bob	refund	pamphlet
Sheet1

 

[Chris Chandler]

FormR -

I swear I tried a countif-index-match before, but not once did I try to give the column a range of "0". Excellent! This works like a charm. I was really worried I was going to bog things down with a heavy array formula. Thanks for the swift reply!

-Chris

 

[FormR]

FormR -

I swear I tried a countif-index-match before, but not once did I try to give the column a range of "0". Excellent! This works like a charm. I was really worried I was going to bog things down with a heavy array formula. Thanks for the swift reply!

-Chris

You're welcome - (as I'm sure you have now figured out) zero can be used to tell the index function to return all the values in the row and/or column of the array.