Lookup data from Horizontal set of data

[ExcelLearnerP]

I have Horizontal tables of data.

		AUD	EUR			USD	EUR			INR	EUR
AUDA		150	100	USDA		67	90	INRA		30	80
AUDB		200	150	USDB		117	140	INRB		80	130
AUDC		250	200	USDC		167	190	INRC		130	180

<tbody>
</tbody>

Explanation of above table:
1) AUDA, AUDB, AUDC, USDA, USDB, USDC, INRA, INRB & INRC are bank accounts.
2) The numerical figures are bank balances. For eg:- 150 is the balance AUDA account in AUD (Australian dollar) terms, whereas 100 is the balance of the same account in EUR (Euro) terms and so on..

What I need:

I need the data mentioned above in vertical format mentioned below:

Account	Forex	Forex Balance	GBP Balance
AUDA	AUD	150	100
AUDB	AUD	200	150
AUDC	AUD	250	200
USDA	USD	67	90
USDB	USD	117	140
USDC	USD	167	190
INRA	INR	30	80
INRB	INR	80	130
INRC	INR	130	180

<tbody>
</tbody>

I am a beginner in Excel, and I tried using the Index match function, offset match function quite unsuccessfully. I know that Vlookup can be used, but I will have to use it a number of times, since I have pasted only a sample data above. There are more than 30 different currencies with more than 100 accounts.

I would be really grateful if I can get a solution to my above problem.

Thanks,
P

 

[ExcelLearnerP]

Please read "GBP balance" as "EUR balance" in the 'What I need' table.

 

[sandy666]

with PowerQuery brute force way

create tables (from source) for each currency (here are 3 + EUR common for each)
append all these tables and

[SIZE=1]let
    Source = Table.Combine({#"Table1 (2)", #"Table1 (3)", #"Table1 (4)"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"AUD", "USD", "INR"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Only Selected Columns",{"Column1", "Attribute", "Value", "EUR"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1", "Account"}, {"Attribute", "Forex"}, {"Value", "Forex Balance"}, {"EUR", "GBP Balance"}})
in
    #"Renamed Columns"[/SIZE]

Account	Forex	Forex Balance	GBP Balance
AUDA	AUD	150​	100​
AUDB	AUD	200​	150​
AUDC	AUD	250​	200​
USDA	USD	67​	90​
USDB	USD	117​	140​
USDC	USD	167​	190​
INRA	INR	30​	80​
INRB	INR	80​	130​
INRC	INR	130​	180​

edit:
example

 

[ExcelLearnerP]

Thanks for your reply Sandy666!!

Is it not possible using formulas like Index, Match, Offset or the likes? Because, the excel file I am working on is a Company property and we are not allowed to make changes to the basic structure of the files. We are not allowed to add add-ins to the file.

Regards,
Pratish

 

[sandy666]

ADVERTISEMENT

maybe it willl be possible with any formula (s), I really don't know.
you didn't show your excel version so hard to say you need add-in or not

if you want formula solution you need to wait for someone else who will give you this.

Have a nice day

 

[ExcelLearnerP]

Okies. Thanks Sandy!!

I need a formula solution. I use Excel 2010 btw.

Wish you a great day

 

[sandy666]

FYI

Excel 2010 / 2013 - PowerQuery add-in
Excel 2016 and above - PowerQuery (Get&Transform) built-in