OaklandJim
Well-known Member
- Joined
- Nov 29, 2018
- Messages
- 870
- Office Version
- 365
- Platform
- 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).
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.
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
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.
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