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]

In post #1, your rows are labelled Sam, dave & mike in identical order in each sheet. Therefore I assumed that is what you have. Can you confirm that?
If not, you need to give a clearer picture of what you do actually have.

 

[MikeWall]

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

Its not fully doing it., its bringing in the results from sheet 2, in the same order as sheet 2. but not in the order as lookup keys in sheet one.

It returns the right columns, however the look up keys could be in a different order.

I hope this makes sense

 

[Peter_SSs]

Any chance then that you could make a small set of dummy data and expected results and post with XL2BB?

 

[MikeWall]

we can use your dummy data as an example. We assume that the order of the look up keys are the same in sheet 1 & 2. but in this case they are seldom the case. I should have said this.

 

[MikeWall]

Your formula assume the lookup key orders doesn't change. In the last 2 screen grab i change the order to mike same and dave and still. get the same results as mike same and dave

Lookupkeys	Mexico	Ireland	Europe	Jersey
mike	a	d	m
sam	b	e	n
dave	c	f	o
Lookupkeys	Mexico	Ireland	Europe	Jersey
Sam	a	d	m
dave	b	e	n
mike	c	f	o
	Mexico	Ireland	Washington	United states
Sam	a	d	g	j
dave	b	e	h	k
mike	c	f	i	l
Lookupkeys	Mexico	Ireland	Europe	Jersey
mike	a	d	m
sam	b	e	n
dave	c	f	o

 

[Peter_SSs]

Your formula assume the lookup key orders doesn't change.

Correct, that is what I stated in post #11 because that is what you posted originally.

In post #1, your rows are labelled Sam, dave & mike in identical order in each sheet. Therefore I assumed that is what you have.

Try this instead

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

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