Dynamic header match

[MikeWall]

Hi Guys,

I have been trying to come up with a formula for an issue

In sheet one , i have a result sheet where i want return data set from sheet 2.

The criteria for choosing the results are 2 fold, based on lookupkeys, then criteria which is country).

However the issue is, in sheet two, the columns could change, such as Column Y could be new jersey today and tomorrow it could be mexico.

What formula could i use that returns the coulmn based of the matched criteria now matter where it was?

	criteria
Lookupkeys	Italy	Ireland	Europe	Jersey
Sam
dave
mike
	Dataset
	Mexico	Ireland	Washington	United states	Europe
Sam
dave
mike

 

[Peter_SSs]

What Excel version are you using? Please update your Account details (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.

 

[MikeWall]

Hi Peter, I am using excel 2016. Account details updated now.

 

[Peter_SSs]

Account details updated now.

Thanks.

Is is something like this that you are after?

MikeWall 2020-04-02 1.xlsm
	A	B	C	D	E	F
1		Mexico	Ireland	Washington	United states	Europe
2	Sam	a	d	g	j	m
3	dave	b	e	h	k	n
4	mike	c	f	i	l	o
Sheet2

Formula in b2 copied across and down.

MikeWall 2020-04-02 1.xlsm
	A	B	C	D	E
1	Lookupkeys	Italy	Ireland	Europe	Jersey
2	Sam		d	m
3	dave		e	n
4	mike		f	o
Sheet1

Cell Formulas
Range	Formula
B2:E4	B2	=IFERROR(INDEX(Sheet2!$B$2:$F$4,ROWS(B$2:B2),MATCH(B$1,Sheet2!$B$1:$F$1,0)),"")

 

[MikeWall]

I didnt get the above to work. is there a way to download your file?

 

[Peter_SSs]

is there a way to download your file?

Create a new file with Sheet1 and Sheet2 then click this icon

on each sheet in my post & paste into Excel on the same top left cell in each sheet

 

[MikeWall]

I didnt get the above to work. is there a way to download your file?

Edit -

I have played around with this. much appreciated

In sheet one, my title would be fixed but my look up keys can be different. In sheet two, headers will have same title as sheet 1, but the columns could change from day to day.

I want to match look up keys in sheet one to sheet 2, then match column headers in sheet one vs sheet 2 - then return the corresponding columns based on the lookup keys.

 

[Peter_SSs]

As far as I can understand what you are saying, I thought that is what my formulas do.
You didn't actually post any sample data or expected results.
In what way is my sample data or results not what you have or want?
Perhaps you could actually post some sample data and expected results with XL2BB

 

[MikeWall]

As far as I can understand what you are saying, I thought that is what my formulas do.
You didn't actually post any sample data or expected results.
In what way is my sample data or results not what you have or want?
Perhaps you could actually post some sample data and expected results with XL2BB

Apologies, yes I see this now. How is the match using the lookup keys working?

 

[Peter_SSs]

How is the match using the lookup keys working?

Before I get involved in too much explanation: Is it actually doing what you want it to do?