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

OaklandJim

Well-known Member
Joined
Nov 29, 2018
Messages
870
Office Version
  1. 365
Platform
  1. Windows
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
BCD
6ProviderPlan
71AetnaPlan G
82Blue ShieldPlan G
93HumanaPlan G
104AnthemPlan G
115AetnaPlan N
126Blue ShieldPlan N
137HumanaPlan N
148HealthnetInnovative
159Blue ShieldPlan 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.
Excel Formula:
=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
 
Maybe something like this:
Book1
ABCDEFG
6ProviderPlanAetnaPlan N
71AetnaPlan G5
82Blue ShieldPlan G
93HumanaPlan G
104AnthemPlan G
115AetnaPlan N
126Blue ShieldPlan N
137HumanaPlan N
148HealthnetInnovative
159Blue ShieldPlan G Extra
Sheet1
Cell Formulas
RangeFormula
F7F7=IFERROR(MATCH(F6&"/"&G6,C7:C15&"/"&D7:D15,0),"""NoMatch")
 
Upvote 0
Hello, are you looking for something like:

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

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top