A formula that combines a lookup/match with transpose

[twilcox1978]

I have a sheet that 8 columns and I need five of them to be transposed in a certain order.

I could not upload a pic for some reason so here goes

A B C D E F G H
1 2 3 4 5 6 7 8

Is there a way to have it look for columns A, C, D, H, and G in that order and transpose them to look like below

A 1
C 3
D 4
H 8
G 7

[mcmahobt]

Your current workbook shows (I'm assuming) your desired result of the data. What does your data look like beforehand?

[twilcox1978]

I can't get the site to take down the wrong file and add the new one. The example in the first post is as close to it as I can get. The top is what is there now and the bottom is what it needs to look like.

[Jonmo1]

Nevermind, misread post.

[FlameRetired]

I don't know if you are committed to using LOOKUP / MATCH / TRANSPOSE, but this gets the job done. Reworked workbook is attached. I used the data supplied in your initial post to make the transpositions clearer to see.

This formula is an array-entered formula that must be committed to the whole pre-selected range at once ..... committed
by pressing and holding Ctrl + Shift while hitting Enter.

Please Login or Register  to view this content.

BTW: You can replace the hard coded {1;3;4;8;7} in that formula with a row-wise column of cells containing those designated column numbers. It makes the formula more flexible. For example:

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E	F	G	H		In A5:B12={=IF(ISERROR(INDEX($A$1:$H$2,{1,2},{1;3;4;8;7})),"",INDEX($A$1:$H$2,{1,2},{1;3;4;8;7}))}
2	1	2	3	4	5	6	7	8
3
4
5	A	1
6	C	3
7	D	4
8	H	8
9	G	7
10
11
12

[FlameRetired]

Here's another. With LOOKUP list in M1:M5.

Please Login or Register  to view this content.

This is not an array formula.

Row\Col

A

B

C

D

E

F

G

H

I

J

K

L

M

1

A

B

C

D

E

F

G

H

A

2

1

2

3

4

5

6

7

8

C

3

D

4

A

1

H

5

C

3

G

6

D

4

7

H

8

8

G

7

[FlameRetired]

And here's another non-array formula.

Please Login or Register  to view this content.

with lookup table still in column M.