Lookup Based on Criteria

[Amosbroker]

Hello everyone!

I'm not sure if I am just having a moment or what but cant seem to figure out how to pull in some data. I have workbook with two tabs. The first tab contains Member ID information. The second tab contains multiple diseases and episodes along with the member ID. Basically I am telling Excel - If B2 on second tab = Diabetes, then match corresponding Member ID with cell in B1 on first tab and pull in the severity level to be displayed on first tab.

First tab - Member ID and Disease, wanting severity level pulled on form second tab
Second Tab - Multiple disease listed with cooresponding severity levels

First Tab:
[TABLE="width: 323"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Member ID[/TD]
[TD]Severity Level [/TD]
[/TR]
[TR]
[TD]ABC1234[/TD]
[TD]Want to pull in from second tab based on specific disease[/TD]
[/TR]
[TR]
[TD]ABC1246[/TD]
[TD]Want to pull in from second tab based on specific disease[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Member ID

Second Tab:
[TABLE="width: 507"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Member ID[/TD]
[TD]Long Description[/TD]
[TD]Major Practice Category[/TD]
[TD]Severity Level[/TD]
[/TR]
[TR]
[TD]ABC1234[/TD]
[TD]Diabetes[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ABC1246[/TD]
[TD]Diabetes[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ABC1247[/TD]
[TD]Diabetes[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ABC1248[/TD]
[TD]Diabetes[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]ABC1249[/TD]
[TD]Diabetes[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ABC1250[/TD]
[TD]Diabetes[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]ABC1251[/TD]
[TD]Other metabolic disorders[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]ABC1259[/TD]
[TD]Other metabolic disorders[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]ABC1257[/TD]
[TD]Other metabolic disorders[/TD]
[TD]Endocrinology[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ABC1258[/TD]
[TD]Cerebral vascular disease[/TD]
[TD]Neurology[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ABC1259[/TD]
[TD]Cerebral vascular disease[/TD]
[TD]Neurology[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]ABC1260[/TD]
[TD]Cerebral vascular disease[/TD]
[TD]Neurology[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]ABC1261[/TD]
[TD]Cerebral vascular disease[/TD]
[TD]Neurology[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]ABC1259[/TD]
[TD]Brain trauma[/TD]
[TD]Neurology[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Array formula to be confirmed by CSE:

Book1
	A	B	C	D
1	Member ID	Description	Severity Level
2	ABC1234	Diabetes	3	3
3	ABC1246	Diabetes	3	3
4	ABC9999	Diabetes
5	ABC1259	Brain trauma		4
6
7			Array formula	Array formula
8			Description hard-coded	Description cell-referenced
Sheet149

Cell Formulas
Range	Formula
C2	{=IFERROR(INDEX(Sheet153!D$2:D$15,MATCH(A2&"Diabetes",Sheet153!A$2:A$15&Sheet153!B$2:B$15,0)),"")}
D2	{=IFERROR(INDEX(Sheet153!D$2:D$15,MATCH(A2&B2,Sheet153!A$2:A$15&Sheet153!B$2:B$15,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D
1	Member ID	Long Description	Major Practice Category	Severity Level
2	ABC1234	Diabetes	Endocrinology	3
3	ABC1246	Diabetes	Endocrinology	3
4	ABC1247	Diabetes	Endocrinology	3
5	ABC1248	Diabetes	Endocrinology	4
6	ABC1249	Diabetes	Endocrinology	3
7	ABC1250	Diabetes	Endocrinology	4
8	ABC1251	Other metabolic disorders	Endocrinology	4
9	ABC1259	Other metabolic disorders	Endocrinology	4
10	ABC1257	Other metabolic disorders	Endocrinology	3
11	ABC1258	Cerebral vascular disease	Neurology	3
12	ABC1259	Cerebral vascular disease	Neurology	4
13	ABC1260	Cerebral vascular disease	Neurology	4
14	ABC1261	Cerebral vascular disease	Neurology	4
15	ABC1259	Brain trauma	Neurology	4
Sheet153

Change cell references/range and sheet name to match yours.

If your "second tab" data are never repeated, there are a couple of Non-array options.

 

[Amosbroker]

Thank you so much! This is what I needed!

 

[jtakw]

You're welcome.