Indexing & Matching from two tables with multiple criteria: resulting with a column header label

[Yukontornado]

Here is my scenario:
I have a need to find a particular percentile from a header row on another sheet in a separate tab within the same workbook, based on criteria from two columns in another worksheet. See example below:

PROVIDER	PROVIDER TYPE	Provider Specialty	Provider FTE	wRVU (adjusted to 1.0)	% tile using wRVU	Comp (adjusted to 1.0)	% tile using Comp
Dr. X	Physician	Endocrinology/Metabolism	1.0	4651.91		$ 239,699.20
DR. Y	Physician	Family Medicine: Ambulatory Only (No Inpatient Work)	1.0	5429.04		$ 232,500.00
Dr. Z	Physician	Gastroenterology	0.6	2537.06		$ 375,000.00

<colgroup><col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="142" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5034;"> <col width="342" style="width: 256pt; mso-width-source: userset; mso-width-alt: 12145;"> <col width="116" style="width: 87pt; mso-width-source: userset; mso-width-alt: 4124;"> <col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 4920;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4551;"> <col width="143" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5091;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4522;"> <tbody> </tbody>

In column F I need to find corresponding percentile from the example below using the wRVU number in column E above (4651.91) and provider specialty in column C (Endocrinology/Metabolism) as
criteria. So looking at the sheet below (different tab in same workbook):

Provider Specialty	49 % tile	Median	51 % tile	52 % tile	53 % tile	54 % tile	55 % tile	56 % tile	57 % tile	58 % tile	59 % tile	60 % tile
Allergy/Immunology	4,610.14	4,666.22	4,690.18	4,767.36	4,829.86	4,848.83	4,878.50	4,904.01	4,981.23	4,987.04	5,032.28	5,060.77
Anesthesiology: Pain Management	6,459.27	6,548.00	6,636.84	6,860.75	6,904.00	6,948.27	7,146.98	7,166.97	7,272.04	7,272.87	7,384.55	7,452.44
Bariatrics (Nonsurgical)	*	*	*	*	*	*	*	*	*	*	*	*
Cardiology: Electrophysiology	10,701.56	10,797.47	10,875.30	10,972.12	11,044.02	11,142.62	11,357.00	11,413.87	11,435.48	11,466.19	11,589.21	11,640.98
Cardiology: Invasive	8,129.04	8,241.31	8,340.00	8,387.00	8,420.66	8,484.29	8,506.99	8,561.60	8,603.98	8,675.74	8,755.83	8,797.00
Cardiology: Invasive-Interventional	9,235.54	9,308.29	9,373.56	9,551.65	9,627.72	9,692.36	9,763.27	9,852.49	9,901.61	9,963.64	10,033.46	10,181.63
Cardiology: Noninvasive	7,119.20	7,218.28	7,333.87	7,394.61	7,502.35	7,565.23	7,727.72	7,806.92	7,917.63	8,006.31	8,088.41	8,193.76
Critical Care: Intensivist	3,804.34	3,848.44	4,015.23	4,030.30	4,063.41	4,084.90	4,095.12	4,118.93	4,224.88	4,261.08	4,333.37	4,377.79
Dentistry	*	*	*	*	*	*	*	*	*	*	*	*
Dermatology	6,806.56	6,853.32	6,889.20	7,002.40	7,037.00	7,131.00	7,233.64	7,301.79	7,401.25	7,589.29	7,684.78	7,752.97
Dermatology: Dermatopathology	*	*	*	*	*	*	*	*	*	*	*	*
Dermatology: Mohs Surgery	15,950.92	15,976.00	16,028.74	16,081.49	16,140.81	16,202.61	16,244.83	16,263.55	16,325.18	16,579.91	16,834.63	17,293.46
Emergency Medicine	6,448.24	6,525.79	6,576.06	6,603.72	6,689.15	6,720.89	6,786.70	6,901.95	6,994.78	7,090.35	7,219.93	7,272.05
Endocrinology/Metabolism	4,438.28	4,535.80	4,541.32	4,579.92	4,598.82	4,635.95	4,689.72	4,718.21	4,818.35	4,834.58	4,907.83	4,939.98
Family Medicine (with OB)	4,229.64	4,275.37	4,316.19	4,375.07	4,412.63	4,440.15	4,488.99	4,524.96	4,552.27	4,572.84	4,594.11	4,637.47

<colgroup><col width="338" style="width: 253pt; mso-width-source: userset; mso-width-alt: 12003;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2730;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2417;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2645;" span="10"> <tbody> </tbody>

I would want to find the closest value to my search requirement value of (4651.91) from column E and specialty from column C (endocrinology/Metabolism) in the second worksheet and return the column header (percentile) for that closest value. So in this case I would want a return value of “54 % tile” because it is next lowest value from 4651.91 that we are searching for.

Does this make sense of what I need to accomplish? I have been playing with Indexing and matching but with no success to this point. Any help on this solution would be GREATLY appreciated!

Thank you for looking at this for me

 

[Yukontornado]

Well, I came up with a solution that works perfectly - but I sure would like to see if there is an EASIER way to do this. I am sure someone else out there has a need to do a lookup via multiple criteria. Enjoy the solution and I hope someone has a shorter version!

To solve the wRVU quesion: MGMA wRVU 2017


=IFERROR(IF(ISTEXT(LOOKUP(SMALL(INDEX('MGMA wRVU 2017'!$B$12:$CD$209,MATCH(C2,'MGMA wRVU 2017'!$A$12:$A$209,0),),COUNTIF(INDEX('MGMA wRVU 2017'!$B$12:$CD$209,MATCH(C2,'MGMA wRVU 2017'!$A$12:$A$209,0),),"<="&E2)+1),INDEX('MGMA wRVU 2017'!$B$12:$CD$209,MATCH(C2,'MGMA wRVU 2017'!$A$12:$A$209,0),0),'MGMA wRVU 2017'!$B$11:$CD$11)),
LOOKUP(SMALL(INDEX('MGMA wRVU 2017'!$B$12:$CD$209,MATCH(C2,'MGMA wRVU 2017'!$A$12:$A$209,0),),COUNTIF(INDEX('MGMA wRVU 2017'!$B$12:$CD$209,MATCH(C2,'MGMA wRVU 2017'!$A$12:$A$209,0),),"<="&E2)+1),INDEX('MGMA wRVU 2017'!$B$12:$CD$209,MATCH(C2,'MGMA wRVU 2017'!$A$12:$A$209,0),0),'MGMA wRVU 2017'!$B$11:$CD$11),
">90th %tile"),"")

 

[Yukontornado]

Edit to my solution above - this will actually pick 55%, which still works for me.