Conditional Lookup Help

[bhope78]

Hello trying to solve a conditional lookup problem was thinking a conditional vlookup or a If(and( statement but I am not sure what the syntax should be.

Thanks in advance.

 

[bhope78]

Here is the XL2BB code not sure how this works....

Conditional Lookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3	Prior Month	Current Month	Expected Result
4	0	0	No Episode in Period	If prior month is equal to zero and current month is equal to zero then return "No Episode in Period"
5	0	1	Episode in current month	If prior month is equal to zero and current month is greater than zero then return "Episode in Current Month"
6	1	0	No Recidivate	If prior month is greater than zero and current month is equal to zero then return"No Recidivate"
7	1	1	Recidivate	If prior month is greater than zero and current month is greater than zero then return "Recidivate"
8	2	0	No Recidivate
9	0	2	Episode in current month
10	2	2	Recidivate
11	2	1	Recidivate
12
13
Sheet1

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	J	K	L
1
2	0	0	No epsiode
3	0	1	Epsiode
4	1	0	No recidivate
5	1	1	Recividate
6	2	0	No recidivate
7	0	2	Epsiode
8	2	2	Recividate
9	2	1	Recividate
Main

Cell Formulas
Range	Formula
L2:L9	L2	=IF(J2=0,IF(K2=0,"No epsiode","Epsiode"),IF(K2=0,"No recidivate","Recividate"))

 

[bhope78]

Hi Fluff,

Not sure how to parse that code that you that you generated it is showing as XL2BB code but I am not sure how to render it in excel format so I can see your excel formula....

 

[jtakw]

Hi,

Here's my version:

Book3.xlsx
	A	B	C
1	Prior Month	Current Month
2	0	0	No Episode in Period
3	0	1	Episode in Current Month
4	1	0	No Recidivate
5	1	1	Recidivate
6	2	0	No Recidivate
7	0	2	Episode in Current Month
8	2	2	Recidivate
9	2	1	Recidivate
Sheet783

Cell Formulas
Range	Formula
C2:C9	C2	=IF(A2,IF(B2,"Recidivate","No Recidivate"),IF(B2,"Episode in Current Month","No Episode in Period"))

Edit/PS: You can just copy and paste the formula(s) for testing.

 

[Fluff]

The formula is shown below the mini sheet, also if you click one of the cells with a formula it will appear in the formula bar of the mini sheet.

 

[bhope78]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	J	K	L
1
2	0	0	No epsiode
3	0	1	Epsiode
4	1	0	No recidivate
5	1	1	Recividate
6	2	0	No recidivate
7	0	2	Epsiode
8	2	2	Recividate
9	2	1	Recividate
Main

Cell Formulas
Range	Formula
L2:L9	L2	=IF(J2=0,IF(K2=0,"No epsiode","Epsiode"),IF(K2=0,"No recidivate","Recividate"))

That worked!

Thank You!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Fluff]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Conditional lookup help needed
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.