Lookup where Column and Row can move

[jimbomcmucka]

Hi guys,
I'm hoping you can help me to expand on the below formula that I have created. Cell B3 gives me a user name and I need to return more details about that user from the tab 'DATA-USERS'. Simply, I am trying to return the organization of the user. Unfortunately the Username is not the first column from the data source, so I am having to use INDEX+MATCH

=INDEX('DATA-USERS'!A:AC,MATCH(B3,'DATA-USERS'!K:K,0),MATCH("Organization",'DATA-USERS'!$1:$1,0))

This formula is working for me, however the issue is that the column can change when new data is extracted from the source. So, I need a way to make the K:K (highlighted in bold) dynamic.

Any suggestions?

Thanks in advance for any help, I;ve been staring at this for far too long and my brain seems to have clocked out for the day.

 

[awoohaw]

you can use the index function to build a new lookup range as follows:
the first argument of the index function would need to include ALL possible columns of data in the look up sheet.

If you want to post your two worksheets (dummied up for anonymity) Someone or I can be more specific.
Use the xl2bb add in to do that.

Book1
	G	H	I	J	K	L	M	N	O	P
1	Cat1	Cat2	Cat3	Cat4	OrgName	Cat6	Cat7	Cat8	Cat9	Cat10
2	GBI	HXF	IJS	JOX	KNA	LUV	MTP	NYU	OTC	PLL
3	GMR	HSP	IUM	JMV	KEQ	LRN	MDG	NLG	OCI	PQD
4	GFV	HUV	IEI	JQB	KBT	LPQ	MBF	NAD	OKF	PWK
5	GKR	HEY	ITX	JNE	KSE	LBL	MJH	NUY	OSB	PAX
6	GXD	HWM	IUP	JYV	KOY	LVO	MSP	NOS	OHM	PKA
7	GDI	HAT	IOT	JCF	KGO	LPF	MYR	NKX	OPO	POF
8	GVX	HHS	IWU	JCD	KXH	LEG	MXD	NTH	OVB	PUC
9	GCO	HPX	IAL	JUD	KVB	LRK	MEJ	NUE	OJX	PJO
10	GDO	HRL	IUE	JGR	KRP	LKW	MOC	NDN	OOP	PPI
11	GSD	HVU	IBJ	JKN	KXV	LWG	MHD	NLK	OEN	PPT
12
13
14	=INDEX($G$1:$P$11,0,MATCH("OrgName",$G$1:$P$1,0))
15	OrgName
16	KNA
17	KEQ
18	KBT
19	KSE
20	KOY
21	KGO
22	KXH
23	KVB
24	KRP
25	KXV
jimbomcmucka

Cell Formulas
Range	Formula
G14	G14	=FORMULATEXT(G15)
G15:G25	G15	=INDEX($G$1:$P$11,0,MATCH("OrgName",$G$1:$P$1,0))
Dynamic array formulas.

 

[jimbomcmucka]

This is great, thanks! I've managed to get it working.

A new feature to add to the list!

 

[awoohaw]

I'm pleased you found a solution here.

Best Wishes!