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:

Member ID	Severity Level
ABC1234	Want to pull in from second tab based on specific disease
ABC1246	Want to pull in from second tab based on specific disease

<colgroup><col><col></colgroup><tbody>
</tbody>

Member ID

Second Tab:

Member ID	Long Description	Major Practice Category	Severity Level
ABC1234	Diabetes	Endocrinology	3
ABC1246	Diabetes	Endocrinology	3
ABC1247	Diabetes	Endocrinology	3
ABC1248	Diabetes	Endocrinology	4
ABC1249	Diabetes	Endocrinology	3
ABC1250	Diabetes	Endocrinology	4
ABC1251	Other metabolic disorders	Endocrinology	4
ABC1259	Other metabolic disorders	Endocrinology	4
ABC1257	Other metabolic disorders	Endocrinology	3
ABC1258	Cerebral vascular disease	Neurology	3
ABC1259	Cerebral vascular disease	Neurology	4
ABC1260	Cerebral vascular disease	Neurology	4
ABC1261	Cerebral vascular disease	Neurology	4
ABC1259	Brain trauma	Neurology	4

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.