Extremely Challenging LOGIC formula Help Request

[nhbartos]

Hi folks,

This one may be tough, if possible at all.

I have my file here if you need it: RSC Billing Template_8-23-17_Test.xlsm - Google Drive

The formula I need is going to be in the H column, Insurance Company, from H6, down to row 500.

I am not sure you can do this with so many ' variables', but here it is...

Formula for H6:

If...
Student 1 is in cell A6, Look for Student 1 in column J.
If student 1 is in column J, insert contents of column K (same row), into column H (same row).
If student 1 is in columns J, insert contents of column L (same row), into column F (same row).

If...
Student 2 is in cell A6, Look for Student 2 in column J.
If student 2 is in column J, insert contents of column K (same row), into column H (same row).
If student 2 is in columns J, insert contents of column L (same row), into column F (same row).

If...
Student 3 is in cell A6, Look for Student 3 in column J.
If student 3 is in column J, insert contents of column K (same row), into column H (same row).
If student 3 is in columns J, insert contents of column L (same row), into column F (same row).

etc...

There can be up to 200 students listed in column J, from J6:J205.

If there is no student name in column A, columns F and H should be blank.

Vince

 

[Scott Huish]

It's basically a VLOOKUP formula.

In F6:
=IFERROR(T(VLOOKUP(A6,$J$6:$N$37,3,0)),"")

In H6:
=IFERROR(VLOOKUP(A6,$J$6:$N$37,2,0),"")

Copy down.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Child (Student Name)	Therapist Name	Start Time	End Time	Session Minutes	CPT Code	Billable Units	Insurance Company		List Student Names Here	Insurance Company x-ref	List CPT Codes Here	List CPT Code Minutes	CPT Code Unit
6	Student 1	T1	8/17/2017 9:00:00 AM	8/17/2017 10:00:00 AM	60.00	H2019	4.00	Anthem		Student 1	Anthem	H2019	15	1
7	Student 2	T2	8/17/2017 10:00:00 AM	8/17/2017 11:00:00 AM	60.00	H2020	4.00	Anthem		Student 2	Anthem	H2020	15	1
8	Student 3	T3	8/17/2017 11:00:00 AM	8/17/2017 12:00:00 PM	60.00	H2020	4.00	Optima		Student 3	Optima	H2020	30	1
9	Student 4	T4	8/17/2017 12:00:00 PM	8/17/2017 1:00:00 PM	60.00	H2021	2.00	Medicaid		Student 4	Medicaid	H2021	30	1
10	Student 5	T5	8/17/2017 1:00:00 PM	8/17/2017 1:30:00 PM	30.00			Anthem		Student 5	Anthem
11	Student 6	T6	8/17/2017 12:00:32 PM	8/17/2017 12:30:37 PM	30.08			Optima		Student 6	Optima
12	Student 7	T7	8/17/2017 12:30:00 PM	8/17/2017 1:00:00 PM	30.00			Medicaid		Student 7	Medicaid
13	Student 8	T8	8/17/2017 1:00:00 PM	8/17/2017 2:00:00 PM	60.00			Anthem		Student 8	Anthem
14	Student 9	T9	8/17/2017 2:00:00 PM	8/17/2017 3:00:00 PM	60.00			Optima		Student 9	Optima
15	Student 10	T10	8/17/2017 12:00:00 PM	8/17/2017 12:30:00 PM	30.00			Medicaid		Student 10	Medicaid
16	Student 11	T1	8/17/2017 12:30:00 PM	8/17/2017 1:00:00 PM	30.00			Anthem		Student 11	Anthem
17	Student 12	T2	8/17/2017 1:00:00 PM	8/17/2017 2:00:00 PM	60.00			Optima		Student 12	Optima
18	Student 13	T3	8/17/2017 2:00:00 PM	8/17/2017 3:00:00 PM	60.00			Medicaid		Student 13	Medicaid
19	Student 14	T4	8/17/2017 10:00:00 AM	8/17/2017 11:00:00 AM	60.00			Medicaid		Student 14	Medicaid
20	Student 15	T5	8/17/2017 11:00:54 AM	8/17/2017 12:00:00 PM	59.10			Anthem		Student 15	Anthem
21	Student 16	T6	8/17/2017 12:00:00 PM	8/17/2017 12:30:00 PM	30.00			Anthem		Student 16	Anthem
22	Student 17	T7	8/17/2017 12:30:00 PM	8/17/2017 1:00:00 PM	30.00			Anthem		Student 17	Anthem
23	Student 18	T8	8/17/2017 1:00:00 PM	8/17/2017 2:00:00 PM	60.00			Anthem		Student 18	Anthem
24	Student 19	T9	8/17/2017 9:00:00 AM	8/17/2017 10:00:00 AM	60.00			Optima		Student 19	Optima
25	Student 20	T10	8/17/2017 10:00:00 AM	8/17/2017 11:00:00 AM	60.00			Medicaid		Student 20	Medicaid
26	Student 21	T1	8/17/2017 11:00:00 AM	8/17/2017 12:00:00 PM	60.00			Anthem		Student 21	Anthem
27	Student 22	T2	8/17/2017 9:00:00 AM	8/17/2017 10:00:00 AM	60.00			Anthem		Student 22	Anthem
28	Student 23	T3	8/17/2017 10:00:00 AM	8/17/2017 11:00:00 AM	60.00			Optima		Student 23	Optima
29	Student 24	T4	8/17/2017 11:00:08 AM	8/17/2017 12:00:12 PM	60.07			Medicaid		Student 24	Medicaid
30	Student 25	T5	8/17/2017 12:00:00 PM	8/17/2017 1:00:00 PM	60.00			Anthem		Student 25	Anthem
31	Student 26	T6	8/17/2017 1:00:00 PM	8/17/2017 2:00:00 PM	60.00			Anthem		Student 26	Anthem
32	Student 27	T7	8/17/2017 9:00:00 AM	8/17/2017 10:00:00 AM	60.00			Optima		Student 27	Optima
33	Student 28	T8	8/17/2017 10:00:00 AM	8/17/2017 11:00:00 AM	60.00			Medicaid		Student 28	Medicaid
34	Student 29	T9	8/17/2017 11:00:00 AM	8/17/2017 11:30:00 AM	30.00			Anthem		Student 29	Anthem
35	Student 30	T10	8/17/2017 11:30:00 AM	8/17/2017 12:00:00 PM	30.00			Anthem		Student 30	Anthem
36	Student 31	T1	8/17/2017 12:00:00 PM	8/17/2017 1:00:00 PM	60.00			Optima		Student 31	Optima
37	Student 32	T2	8/17/2017 1:00:00 PM	8/17/2017 2:00:00 PM	60.00			Medicaid		Student 32	Medicaid
Step 1