Index Match Array or Similar required

[jthompson_25]

Dear all,

I have a data table of currency values, the top row contains all the currency pairings (GBP/EUR, EUR/USD etc). Whilst the columns on the LHS of the sheet contain information on the location and owner of the currency.

I am trying to write a formula capable of pulling out all the unique values in a row and transposing them into a list, once the list of numerical values is transposed I then wish to use these unique numerical values to pull out further information from my table.

So far I have tried {=INDEX(D5:T32,MATCH(0,COUNTIF($C$35:C35,D5:D32),0))} unfortunately this returns an N/A error I believe because my arrays are different sizes

In the image I am trying to pull out the orange figure and place it into the template (cells D38:39), once I have the orange figure in the template I can then use other formulas to pull values associated with this.

Thank you in advance for any help provided

 

[jasonb75]

Maybe something like this

=TRANSPOSE(UNIQUE(LET(arr,FILTER($D$5:$D$32,$C$5:$C$32=$D36),FILTER(arr,arr<>""))))

From your post, it is far from clear what you wish to transpose or which unique values should be pulled.

If you want a formula to create the entire bottom table from the top one then that is not transposing, it is total reorganisation which is not going to need multiple formulas to extract the correct details. Something that will need a usable data sample (one posted using XL2BB which you can download and install by clicking the link at the bottom of this reply rather than a screen capture which can not be used for testing formulas).

 

[AlanY]

Not sure if this can help, you can use the unique() function (e.g. in Cell A17) to create a unique list from A1:A15 and then use index/match to extract other info required

Book1
	A
1	A1
2	A2
3	A3
4	A5
5	A5
6	B1
7	B1
8	B3
9	B4
10	B5
11	C2
12	C2
13	C2
14	C4
15	C5
16
17	A1
18	A2
19	A3
20	A5
21	B1
22	B3
23	B4
24	B5
25	C2
26	C4
27	C5
Sheet1

Cell Formulas
Range	Formula
A17:A27	A17	=UNIQUE(A1:A15)
Dynamic array formulas.

 

[jthompson_25]

Thanks Jason, please accept my apologies the original post was not clear. The unique values I want to pull are the numerical values so those highlighted in yellow in the latest screenshot

I tried UNIQUE and believe this could be the ideal solution however I cannot get it to work when my array spans $D$5:$T$32 and I wish to ignore all blanks and duplicate values.

I tried the below but receive a #Value error

=UNIQUE(FILTER($D$5:$T$32,$D$5:$T$32<>""))

 

[Fluff]

How about

=LET(Rng,D5:T32,r,ROWS(Rng),s,SEQUENCE(r*COLUMNS(Rng),,0),I,INDEX(Rng,MOD(s,r)+1,INT(s/r)+1),UNIQUE(FILTER(I,I<>0)))

 

[jthompson_25]

Thank you, the above works perfectly

Separately which formula syntax would be helpful to pull the currency pair from row 2 (highlighted green) associated with each unique numerical value, index + match don't seem to work due to differing array sizes.

as an example -1921724.9 is associated with EURGBP

The formula I tried is below but this returns an N/A error

=INDEX(D2:T2,MATCH(E39,D5:T31,0))

please advise if cleaner to start a new thread

 

[Fluff]

If you had two values the same in different columns, which header should be returned?

 

[jthompson_25]

The first header

 

[Fluff]

Ok, how about

=INDEX($D$2:$T$2,AGGREGATE(15,6,(COLUMN($D$2:$T$2)-COLUMN($D$2)+1)/($D$5:$T$32=E39),1))

 

[jthompson_25]

That works perfectly thank you!