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:

[TABLE="width: 927"]
<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>[TR]
[TD="class: xl71, width: 101"]PROVIDER[/TD]
[TD="class: xl71, width: 142"]PROVIDER TYPE[/TD]
[TD="class: xl71, width: 342"]Provider Specialty[/TD]
[TD="class: xl71, width: 116"]Provider FTE[/TD]
[TD="class: xl71, width: 138"]wRVU
(adjusted to 1.0)[/TD]
[TD="class: xl71, width: 128"]% tile using wRVU[/TD]
[TD="class: xl71, width: 143"]Comp
(adjusted to 1.0)[/TD]
[TD="class: xl71, width: 127"]% tile using Comp[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 101"]Dr. X[/TD]
[TD="class: xl67, width: 142"]Physician[/TD]
[TD="class: xl67, width: 342"]Endocrinology/Metabolism[/TD]
[TD="class: xl68, width: 116"]1.0[/TD]
[TD="class: xl69, width: 138"]4651.91[/TD]
[TD="class: xl72, width: 128"][/TD]
[TD="class: xl73, width: 143"] $ 239,699.20 [/TD]
[TD="class: xl72, width: 127"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 101, bgcolor: transparent"]DR. Y[/TD]
[TD="class: xl67, width: 142, bgcolor: transparent"]Physician[/TD]
[TD="class: xl67, width: 342, bgcolor: transparent"]Family Medicine: Ambulatory Only (No Inpatient Work)[/TD]
[TD="class: xl68, width: 116, bgcolor: transparent"]1.0[/TD]
[TD="class: xl69, width: 138, bgcolor: transparent"]5429.04[/TD]
[TD="class: xl72, width: 128"] [/TD]
[TD="class: xl73, width: 143, bgcolor: transparent"] $ 232,500.00 [/TD]
[TD="class: xl72, width: 127"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 101"]Dr. Z[/TD]
[TD="class: xl67, width: 142"]Physician[/TD]
[TD="class: xl67, width: 342"]Gastroenterology[/TD]
[TD="class: xl68, width: 116"]0.6[/TD]
[TD="class: xl69, width: 138"]2537.06[/TD]
[TD="class: xl72, width: 128"] [/TD]
[TD="class: xl73, width: 143"] $ 375,000.00 [/TD]
[TD="class: xl72, width: 127"] [/TD]
[/TR]
</tbody>[/TABLE]




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


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


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.