VLOOKUP (or OFFSET & MATCH)

[Darrell Hipkiss]

Hi everyone,

I need assistance with a formula I am trying to develop using OFFSET and MATCH.

The attached workbook contains two tabs: PIPELINE & DATA.

The DATA tab will contain my raw data calculations.

The PIPELINE tab will be a read only tab, with the exception of column C (starting C11) which is where I want the formula (reading from DATA tab (column E), depending on the Division (column D).

What I want my formula to achieve: I Want Excel to return each successive GCHHS number (moving down) only if it matches the Division on the PIPELINE tab.

This will need to be a repeatable formula that starts from each new Division on the PIPELINE.

The report will be run monthly and the number of rows may change per Division, so I suspect I will need to manually repeat the formula on the applicable rows in the PIPELINE tab.

I have attached a screen shot but also can attach the file (but wasn't sure how to)

 

[earthworm]

Why everyone share pictures instead of excel to get solutions . Kindly share excel file.

You need to share excel file along with answer that you require for everyone to understand

 

[kweaver]

Darrell: use XL2BB please.

 

[Darrell Hipkiss]

Apologies everyone, I wasn't exactly sure how to upload the file, which I had originally intended to do. I will attempt the XL2BB

 

[Darrell Hipkiss]

MrExcelExample.xlsm
	B	C	D	E	F	G	H	I	J
8	SOURCING PIPELINE FIELD NAME		HHS Division	Ref. No	Purpose	Category (Mega)	Proposed Contract Type (VRM)	Procurement Phase	Estimated Contract Award
9
10	DESCRIPTION / INTERPRETATION / PURPOSE		The requesting HHS Division (or primary division in the case of cross Divisional) who will fund any resulting contract that arises from the procurement	A unique number generated from within the GCHHS Contract Management System (Q-Contracts) where a contract has been established following a procurement activity.	The purpose and objective of the procurement/contract. (150 character limit)	The mega category identified in Qcontracts that links in with the 6x QLD Gov Category Groups	Proposed Contract Type (VRM)	Defines one of the 6x phases of the procurement process If a Contract, this will require manual input to update	This date willl be used as to determine the estimated contract award date for activty arising from: New Procurement
11	QCONTRACT FIELD NAME		Business Level 3	File Reference Number	Description (Title)	Mega Category	Risk Type	Sourcing Stage	Commencement Date
12	DATA SOURCE		Q-Contracts	Q-Contracts	Q-Contracts	Q-Contracts	Q-Contracts	Q-Contracts	Q-Contracts
13	HOW DATA IS OBTAINED		Report Data	Report Data	Report Data	Report Data	Report Data	Report Data	Report Data
14	IF FORMULA REQUIRED		Formula	No	No	Formula	No	Formula	Formula
15
16
17			Allied Health Services	GCHHS-12345
18			Allied Health Services	GCHHS-67891
19			Centre for Health Innovation	GCHHS-52643
20			Surgical, Anaesthetics and Procedural Service	GCHHS-xxxxx
21			Surgical, Anaesthetics and Procedural Service	GCHHS-tttttt
22
23
24			GCHHS-12345
25			GCHHS-67891
26			GCHHS-52643
27			GCHHS-xxxxx
28			GCHHS-tttttt
DATA

Cell Formulas
Range	Formula
D24	D24	=OFFSET($D$17,MATCH("Allied Health Services",$D$17:$D$20,0)-1,1,1)
D25	D25	=OFFSET(D18,MATCH("Allied Health Services",$D$17:$D$20,0)-1,1,1)
D26	D26	=OFFSET(D17,MATCH("Centre for Health Innovation",$D$17:$D$20,0)-1,1,1)
D27:D28	D27	=OFFSET(D17,MATCH("Surgical, Anaesthetics and Procedural Service",$D$17:$D$20,0)-1,1,1)

 

[Darrell Hipkiss]

MrExcelExample.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2			Strategic Sourcing Pipeline
3			The sourcing pipeline identifies specific categories and contracts expected to be re-procured and new procurement projects to be undertaken across the HHS. It provides a projected time-frame for planning, implementation and award activities. The procurement streams (Strategic Procurement, Asset Management Services and Information Technology Services) will consult with the identified HHS divisional areas regarding management of these activities throughout the procurement lifecycle.
4			Gold Coast University Hospital - Financial Services, Strategic Procurement					Reporting Period:						01 Jul 2020		>>	01 Jul 2023
5
6	Click + to expand/collapse			Division
7			Allied Health Services
8			Ref. No	HHS Service Line	Purpose		HHS Strategy			QPP				Category (Mega)	Proc Stream	Cross Division	Proposed Contract Type (VRM)	Activity arising from:		Existing Extension Provisions?	Proposed Sourcing Method
9							Alignment to Corporate Strategies, Operational and Corporate Plans			Social	ATSI	LBT	SME					New Procurement	Expiring Contract
10			Registered Activity No.	HHS Corporate Structure	Description of Requirement / Contract Title		Corporate Strategies	Strat Plan	Op Plan	Queensland Procurement Policy				Category	Proc Area		Refer VRM				Anticipated Market Engagment Strategy
11			Formula to return result in DATA tab column E (if "Allied Health Services")												SP	Yes	Strategic	Yes	Yes	Yes
12			Formula to return result in DATA tab column E (if "Allied Health Services")
13			Formula to return result in DATA tab column E (if "Allied Health Services")
14			Formula to return result in DATA tab column E (if "Allied Health Services")
15			Formula to return result in DATA tab column E (if "Allied Health Services")
16			Formula to return result in DATA tab column E (if "Allied Health Services")
17			Formula to return result in DATA tab column E (if "Allied Health Services")
18			Formula to return result in DATA tab column E (if "Allied Health Services")
19			Formula to return result in DATA tab column E (if "Allied Health Services")
20
21
22
23
24	Click + to expand/collapse			Division
25			Centre for Health Innovation
26			Formula to return result in DATA tab column E (if "Centre for Health Innovation")		Purpose of this contract is ….		Safety and Quality Strategy 2020-2020	Yes	Yes	No	Yes	Yes	Yes	GGS	SP	Yes	Strategic	Yes	Yes	Yes	Closed Invitation to Offer (ITO)
27			Formula to return result in DATA tab column E (if "Centre for Health Innovation")		Purpose of this contract is ….		Digital Strategy 2018-2021	Yes	Yes	Yes	Yes	Yes	Yes	ICT	ICT	Yes	Routine	No	Yes		Utilise SOA arrangement
28								Yes	Yes	Yes	Yes	Yes	Yes			Yes
PIPELINE

Cell Formulas
Range	Formula
N4	N4	=AE10
Q4	Q4	=BO10

Cells with Data Validation
Cell	Allow	Criteria
G19:G23	List	=HHS_Strategies
G13:G14	List	=HHS_Strategies
G16:G17	List	=HHS_Strategies
R11:T11	List	=Yes_No
R14:T14	List	=Yes_No
R17:T17	List	=Yes_No
R20:T23	List	=Yes_No
H21:M23	List	=Yes_No
U21:U23	List	=Sourcing_Method

 

[Darrell Hipkiss]

Hi everyone, sorry Ive adjusted the entire formula. I hope this makes more sense, but the VLOOKUP is still not working

HHS SP Sourcing Pipeline v0.04.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			Strategic Sourcing Pipeline
3			The sourcing pipeline identifies specific categories and contracts expected to be re-procured and new procurement projects to be undertaken across the HHS. It provides a projected time-frame for planning, implementation and award activities. The procurement streams (Strategic Procurement, Asset Management Services and Information Technology Services) will consult with the identified HHS divisional areas regarding management of these activities throughout the procurement lifecycle.
4			Gold Coast University Hospital - Financial Services, Strategic Procurement					Reporting Period:
5
6	Click + to expand/collapse
7			Allied Health Services
8			Ref. No	HHS Service Line	Purpose		HHS Strategy			QPP
9							Alignment to Corporate Strategies, Operational and Corporate Plans			Social	ATSI	LBT	SME
10			Registered Activity No.	HHS Corporate Structure	Description of Requirement / Contract Title		Corporate Strategies	Strat Plan	Op Plan	Queensland Procurement Policy
11			#N/A
12
13
14
15
16
17
18
19
20
21
22
23
24	Click + to expand/collapse
25			Centre for Health Innovation
26			GCHHS-xxxxx	Occupational Therapy	Purpose of this contract is ….		Safety and Quality Strategy 2020-2020	Yes	Yes	No	Yes	Yes	Yes
27			GCHHS-xxxxx	Nutrition and Food Services	Purpose of this contract is ….		Digital Strategy 2018-2021	Yes	Yes	Yes	Yes	Yes	Yes
28			New item					Yes	Yes	Yes	Yes	Yes	Yes
PIPELINE

Cell Formulas
Range	Formula
C11	C11	=VLOOKUP("Allied Health Services"&"-"&DATA!AT17,DATA!$AQ$17:$AS$1500,3,0)

 

[Darrell Hipkiss]

Calculations page:

HHS SP Sourcing Pipeline v0.04.xlsm
	D	E											P																AF											AQ	AR	AS	AT
8	HHS Division	Ref. No											HHS Service Line																CALCULATIONS FOR DASHBOARD
9																													HHS Division											Division Helper	Division	Value Return	Nth
10	The requesting HHS Division (or primary division in the case of cross Divisional) who will fund any resulting contract that arises from the procurement	A unique number generated from within the GCHHS Contract Management System (Q-Contracts) where a contract has been established following a procurement activity.											The requesting HHS service line relating to the division
11	Business Level 3	File Reference Number											Business Level 2
12	Q-Contracts	Q-Contracts											Q-Contracts
13	Report Data	Report Data											Report Data
14	Formula	No											Formula
15
16
17	17 - Gold Coast HHS > 17.12 Allied Health Services > 17.12.04 Nutrition & Food Services	GCHHS73667											Nutrition & Food Services																Allied Health Services											Allied Health Services -1	Allied Health Services	GCHHS73667	1
18	17 - Gold Coast HHS > 17.12 Allied Health Services > 17.12.04 Nutrition & Food Services	GCHHS79907											Nutrition & Food Services																Allied Health Services											Allied Health Services -2	Allied Health Services	GCHHS79907	2
19	17 - Gold Coast HHS > 17.12 Allied Health Services > 17.12.04 Nutrition & Food Services	GCHHS82801											Nutrition & Food Services																Allied Health Services											Allied Health Services -3	Allied Health Services	GCHHS82801	3
20	17 - Gold Coast HHS > 17.12 Allied Health Services > 17.12.04 Nutrition & Food Services	GCHHS83542											Nutrition & Food Services																Allied Health Services											Allied Health Services -4	Allied Health Services	GCHHS83542	4
21	17 - Gold Coast HHS > 17.12 Allied Health Services > 17.12.04 Nutrition & Food Services	GCHHS86306											Nutrition & Food Services																Allied Health Services											Allied Health Services -5	Allied Health Services	GCHHS86306	5
22	17 - Gold Coast HHS > 17.12 Allied Health Services > 17.12.04 Nutrition & Food Services	GCHHS91433											Nutrition & Food Services																Allied Health Services											Allied Health Services -6	Allied Health Services	GCHHS91433	6
23	17 - Gold Coast HHS > 17.12 Allied Health Services > 17.12.04 Nutrition & Food Services	GCHHS70610-20											Nutrition & Food Services																Allied Health Services											Allied Health Services -7	Allied Health Services	GCHHS70610-20	7
24	17 - Gold Coast HHS > 17.01 Surgical, Anaesthetics and Procedural Service > 17.01.02 Head, Neck, Oral & Neurosurgery	GCHHS70610-24											Head, Neck, Oral & Neurosurgery																Surgical, Anaesthetics and Procedural Service											Surgical, Anaesthetics and Procedural Service -1	Surgical, Anaesthetics and Procedural Service	GCHHS70610-24	8
25	17 - Gold Coast HHS > 17.01 Surgical, Anaesthetics and Procedural Service > 17.01.02 Head, Neck, Oral & Neurosurgery	GCHHS70610-25											Head, Neck, Oral & Neurosurgery																Surgical, Anaesthetics and Procedural Service											Surgical, Anaesthetics and Procedural Service -2	Surgical, Anaesthetics and Procedural Service	GCHHS70610-25	9
DATA

Cell Formulas
Range	Formula
AF17:AF25	AF17	=IFERROR(MID(D17,(FIND(">",D17)+8),(FIND(">",D17,(FIND(">",D17)+8)+1))-(FIND(">",D17)+8)),"")
AQ17:AQ25	AQ17	=AF17&"-"&COUNTIF($AF$17:AF17,AF17)
AR17:AR25	AR17	=AF17
AS17:AS25	AS17	=E17
P17:P25	P17	=IFERROR(RIGHT(AO17,LEN(AO17)-10),"")