Return a Vllokup for a column value not knowing where the column in question is.

[Richard_]

Hello There

I'm hoping this is going to be a relatively easy question to answer. My head hurts trying it though. so please Can someone help?

The problem is I have the unique reference down the side in column A (A2-A4000) going downwards and I want to return the data from another sheet based on the column headings going across. - (No problem a VLOOKUP) however,
The spreadsheet I'm drawing the answer from is huge 800 columns and the headed columns are all mixed up on the two sheets (but the headings are the same)

Does anyone know of a formula that can help me return a value using my Column A value against my mixed up Row headings in Row 1 please?

Thanks in advance.

Rich

 

[Toadstool]

Richard,

You can use an INDEX MATCH and MATCH. INDEX lets you retrieve from a grid by specifying the row (we can find this using MATCH on the unique reference in column A) and the column (by using MATCH to find a matching column on both sheets).

Here's my test data "AnotherSheet" with the possible 800 columns:

Book1
	A	B	C	D	E	F
1	Reference	Mineral	Plasma	Animal	Anti-Matter	Vegetable
2	XX-794	Z/40	Z/23	Z/34	Z/80	Z/77
3	XX-236	Z/91	Z/69	Z/86	Z/25	Z/64
4	XX-726	Z/57	Z/49	Z/44	Z/58	Z/80
5	XX-395	Z/92	Z/35	Z/19	Z/89	Z/35
6	XX-303	Z/58	Z/19	Z/51	Z/78	Z/18
7	XX-819	Z/81	Z/9	Z/80	Z/29	Z/12
8	XX-528	Z/58	Z/71	Z/31	Z/57	Z/28
9	XX-292	Z/27	Z/53	Z/57	Z/1	Z/54
10	XX-248	Z/27	Z/88	Z/15	Z/67	Z/48
AnotherSheet

Now for my main sheet I'll put the INDEX MATCH and MATCH into each cell where I want to retrieve a matching reference/heading:

Book1
	A	B	C	D
1	Reference	Animal	Vegetable	Mineral
2	XX-248	Z/15	Z/48	Z/27
3	XX-794	Z/34	Z/77	Z/40
4	XX-726	Z/44	Z/80	Z/57
5	XX-395	Z/19	Z/35	Z/92
6	XX-303	Z/51	Z/18	Z/58
Sheet1

Cell Formulas
Range	Formula
B2:D6	B2	=INDEX(AnotherSheet!$B$2:$XFD$4000,MATCH($A2,AnotherSheet!$A$2:$A$4000,0),MATCH(B$1,AnotherSheet!$B$1:$ADT$1,0))

 

[Richard_]

Thank-you for your response Toadstool - I will be giving it a go tomorrow at work
I also came up with : that seems to work as long as I add in the column numbers in across the page;
.=VLOOKUP(A3,Sheet1!$A$3:$AZF$2017,HLOOKUP(A$2,Sheet1!$A$2:$AZF$2017,2,FALSE),FALSE)
essentially its for trying to create an upload of data from a suppliers sheet when the their data pulled out doesn't quite match the column data on our system to upload it back in. So many thanks for the help.