VLookup/IF formula

[biglb79]

can someone please help me this formula? I'm using a VLookup to determine if column M should be labelled as Inpatient or Outpatient based on the program in column L. There is one program "PHP" that is outpatient, but it should be inpatient for three of the facilities and the facilities are listed in column A. They are labeled as ECC, ECI and ECS

hopefully that makes sense

 

[Joe4]

Can you post a sample of your data, formula, and expected output?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[biglb79]

Can you post a sample of your data, formula, and expected output?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[biglb79]

I think I finally figured it out

census sample file.xlsx
	B
23
Census info

 

[Joe4]

I think I finally figured it out

census sample file.xlsx
	B
23
Census info

You need to select ALL the cells you want to copy/paste here before posting.

 

[biglb79]

You need to select ALL the cells you want to copy/paste here before posting.

ah! here you go

census sample file.xlsx
	A	B	C	D	E		G	H	I	J	K	L	M
16	B/U Code											Program	Inpatient/Outpatient
17	River											ADULT PSYCH
18	ECI											Detox
19	ECI											RTC
20	ECI											RTC
21	ECI											RTC
22	ECI											RTC
23	ECI											Detox
24	Clear											ADULT PSYCH
25	Clear											ADULT PSYCH
26	Clear											ADULT PSYCH
27	Clear											GERI PSYCH
28	Clear											ADULT PSYCH
29	ECS											PHP
30	ECS											RTC
31	ECS											RTC
32	ECS											PHP
33	ECS											RTC
34	Sun											ADULT PSYCH
35	Sun											ADULT PSYCH
36	Sun											ADULT PSYCH
37	Sun											ADULT PSYCH
38	Sun											ADULT PSYCH
39	Sun											ADULT PSYCH
40	Sun											ADULT PSYCH
41	Sun											GERI PSYCH
42	Sun											MH IOP
43	ECC											PHP
44	ECC											RTC
45	ECC											PHP
46	ECC											PHP
Census info

census sample file.xlsx
	A	B
1	Program	IP/OP?
2	ADULT PSYCH	Inpatient
3	Assessment	Inpatient
4	Detox	Inpatient
5	GERI PSYCH	Inpatient
6	IOP	Outpatient
7	MH IOP	Outpatient
8	MH PHP	Outpatient
9	Outpatient	Outpatient
10	PHP	Outpatient
11	RTC	Inpatient
Vista census key

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B11	Cell Value	=#REF!	text	NO

 

[Joe4]

I see. That makes much more sense now, thanks.

Try this formula in cell M17 and copy down for all rows:

=IF(AND(L17="PHP",OR(A17="ECC",A17="ECI",A17="ECS")),"Inpatient",VLOOKUP(L17,'Vista census key'!$A$2:$B$11,2,0))

 

[biglb79]

I see. That makes much more sense now, thanks.

Try this formula in cell M17 and copy down for all rows:

=IF(AND(L17="PHP",OR(A17="ECC",A17="ECI",A17="ECS")),"Inpatient",VLOOKUP(L17,'Vista census key'!$A$2:$B$11,2,0))

perfect!! thanks so much!

 

[Joe4]

You are welcome.
Glad I was able to help!

If you break it down, you can see all that I did was first look for your "special" condition, and if found, populate "Inpatient". Otherwise, do the normal VLOOKUP.