How to get Data row # of data based on a combination of two lookup values

[OaklandJim]

Exceleronz

I have some data shown below. I need to determine the data row # for a combination of two values (i.e., from two differnt columns), hopefully using built-in function(s).

SadieInsurance_ver3o_dev.xlsm
	B	C	D
6		Provider	Plan
7	1	Aetna	Plan G
8	2	Blue Shield	Plan G
9	3	Humana	Plan G
10	4	Anthem	Plan G
11	5	Aetna	Plan N
12	6	Blue Shield	Plan N
13	7	Humana	Plan N
14	8	Healthnet	Innovative
15	9	Blue Shield	Plan G Extra
Medical Plans

So, in the example, I might need to know the data row # for the combination of Aetna (in the provider column) Plan N (in the Plan column) which is 5.

Names of the two data ranges (columns) are Providers_MedPlansData and PlanTypes_MedPlansData.

I tried this -- that I found by Googling -- which returns a #VALUE error.

=XLOOKUP(C11&D11, (Providers_MedPlansData & "," & PlanTypes_MedPlansData), ROW(Providers_MedPlansData))

I even tried using cell address references instead of named ranges but that does not work either. It returns an #NA error.

I hoped to use built-in functions like XLOOKUP but I am about ready to write some code to figure out data row #.

I sure appreciate any assistance.

Jim

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E	F	G
6			Provider	Plan		Aetna	Plan N
7		1	Aetna	Plan G		5
8		2	Blue Shield	Plan G
9		3	Humana	Plan G
10		4	Anthem	Plan G
11		5	Aetna	Plan N
12		6	Blue Shield	Plan N
13		7	Humana	Plan N
14		8	Healthnet	Innovative
15		9	Blue Shield	Plan G Extra
Sheet1

Cell Formulas
Range	Formula
F7	F7	=IFERROR(MATCH(F6&"/"&G6,C7:C15&"/"&D7:D15,0),"""NoMatch")

 

[hagia_sofia]

Hello, are you looking for something like:

=SUM((C7:C15="Aetna")*(D7:D15="Plan N")*(B7:B15))