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))

 

[OaklandJim]

Following up on this thread, I have some different data that does not include row indices. I was still hoping to determine the data row for a combination of first and last name values that are looked up in the data. In the example shown above (assuming data without the indices) I would like to determine the data row number for a combination of Aetna and Plan N. The result should be 5 as that is the row with that combination.

 

[OaklandJim]

I figured it out. I had the syntax wrong. I got it from Google AI.

=XLOOKUP(FirstNamePerson1&LastNamePerson1, (FirstNamesData & LastNamesData), ROW(FirstNamesData)-ROW(Header_FirstNameData))

 

[AhoyNC]

If you just want the ROW# you could also use XMATCH

Book1
	A	B	C	D	E	F
1					Find Row#
2		First	Last		Mary	May
3		Sam	Smith		2
4		Mary	May
5		Larry	Jones
Sheet1

Cell Formulas
Range	Formula
E3	E3	=XMATCH($E$2&$F$2,$B$3:$B$5&$C$3:$C$4,0)