How to find the column index for a combination of two values in two differnet rows.

[OaklandJim]

In a worksheet I have two named ranges: Providers and PlanNames. Providers is a list of insurance providers and PlanNames lists corresponding plan types.

SadeInsurance_ver3h.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
6	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
7	Anthem	Anthem	Blue Shield	Blue Shield	Humana	Humana	SilverScripts	Wellcare	Wellcare	Wellcare	Cigna	Cigna	Cigna	UHC	UHC
8	Basic	Plus	Plus	Enhanced	Value RX	Premier	Choice	Value Script	Classic	Value Plus	Saver Rx	Assurance	Extra	Preferred	Saver
Drugs

Cell Formulas
Range	Formula
D7:W7	D7	=IF(TRANSPOSE('Drug Plans'!Providers)=0,"", TRANSPOSE('Drug Plans'!Providers))
D8:W8	D8	=IF(TRANSPOSE('Drug Plans'!Plans)=0,"", TRANSPOSE('Drug Plans'!Plans))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Drug Plans'!Plans	='Drug Plans'!$C$5:$C$24	D8
'Drug Plans'!Providers	='Drug Plans'!$B$5:$B$24	D7
ProvidersList_DrugPlansWorksheet	=OFFSET('Drug Plans'!Header_Plans,1,0,'Drug Plans'!Entries_Count,1)	D7
'Drug Plans'!rrPlan	='Drug Plans'!$C8	D8
'Drug Plans'!rrProvider	='Drug Plans'!$B7	D7

I need to determine the "data column" index for a provider name/plan type combination. That is, the index sought is the "data column" number not worksheet column.

In a formula I would specify the provider name and plan type and the formula returns the data column index.

Example: the combination of Anthem and Plus returns the index of 2. Example: the combination of Humana Premier returns the index of 6.

I really appreciate any assistance with this.

Have a great day
Jim

 

[OaklandJim]

I figured this out. I added another range called PlanIndeces and used that with XLOOKUP like so...

=XLOOKUP(1, (Drugs!Providers=rcProvider)*(Drugs!PlansNames=rcPlanName),Drugs!ProviderPlansIndeces)

 

[6StringJazzer]

provider name/plan type combination

What cells contain the provider name and plan that you want to look for?

The following formula assumes they are in A1 and A2 respectively, not enough information in your post to know:

=MATCH(A1 & A2, 'Drug Plans'!Providers & 'Drug Plans'!Plans, 0)

This is an array formula but you don't have to do anything special to enter it in 365.

 

[dreid1011]

Hmm, for some reason XL2BB is not cooperating with me right now, but this should suffice:

Formula in D10: =XLOOKUP(D11&D12,D7:R7&D8:R8,D6:R6,"")