Conditional Lookups with Relative Cell Referencing

[chris_bosten]

Hi all,

I hope you can help.

I have the following sheet called "capacity planner":

Planned Date for Job	Day of Week	Job ID	Intervention Type	WO Number	FTE Required
15/01/2025	Wednesday	MJID0018	RTT	WO456720018	1
14/01/2025	Tuesday	MJID0019	RTT	WO456710019	2
13/01/2025	Monday	MJID0001	RTC-I	WO456700001	1
13/01/2025	Monday	MJID0001	RTC-R	WO456700001	1

The FTE required depends on whether "RTC-R" appears in the D column.

If it does, I need the formula to return a value in a separate sheet called "RA Raw Data" from column AJ, using the JOB ID in capacity planner (column C) with the JOB ID in "RA Raw Data" (column R) as the matching value. However, if the information in column D is not "RTC-R" I need the formula to return a value from column AF in "RA Raw Data" using the same matching value.

I have tried
=IF(D2="RTC-R",VLOOKUP('Capacity Planner'!C2:C100000,'RA Raw Data'!R2:AJ100000,19,FALSE),VLOOKUP(C2:C100000,'RA Raw Data'!R2:AF100000,15,FALSE))
and I have also tried a similar index-match forumula.

However, both of these only return values from AF, not in AJ, regardless of whether "RTC-R" appears in column D. I believe it's because of relative referencing in D2. When I write the formula and press enter, all the cells beneath populate with the formula, but they retain "D2" rather than going down "D3","D4" etc.

Is there any way I can fix this?

Best wishes
Chris

 

[etaf]

you need a lookup value
NOT a range
=IF(D2="RTC-R",VLOOKUP('Capacity Planner'!C2,'RA Raw Data'!R2:AJ100000,19,FALSE),VLOOKUP(C2,'RA Raw Data'!R2:AF100000,15,FALSE))
and copy down