Lookup data from Horizontal set of data

[ExcelLearnerP]

I have Horizontal tables of data.

[TABLE="width: 873"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]AUD[/TD]
[TD]EUR[/TD]
[TD][/TD]
[TD][/TD]
[TD]USD[/TD]
[TD]EUR[/TD]
[TD][/TD]
[TD][/TD]
[TD]INR[/TD]
[TD]EUR[/TD]
[/TR]
[TR]
[TD]AUDA[/TD]
[TD][/TD]
[TD]150[/TD]
[TD]100[/TD]
[TD]USDA[/TD]
[TD][/TD]
[TD]67[/TD]
[TD]90[/TD]
[TD]INRA[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]AUDB[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]USDB[/TD]
[TD][/TD]
[TD]117[/TD]
[TD]140[/TD]
[TD]INRB[/TD]
[TD][/TD]
[TD]80[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]AUDC[/TD]
[TD][/TD]
[TD]250[/TD]
[TD]200[/TD]
[TD]USDC[/TD]
[TD][/TD]
[TD]167[/TD]
[TD]190[/TD]
[TD]INRC[/TD]
[TD][/TD]
[TD]130[/TD]
[TD]180[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 354"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Forex[/TD]
[TD]Forex Balance[/TD]
[TD]GBP Balance[/TD]
[/TR]
[TR]
[TD]AUDA[/TD]
[TD]AUD[/TD]
[TD]150[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]AUDB[/TD]
[TD]AUD[/TD]
[TD]200[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]AUDC[/TD]
[TD]AUD[/TD]
[TD]250[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]USDA[/TD]
[TD]USD[/TD]
[TD]67[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]USDB[/TD]
[TD]USD[/TD]
[TD]117[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]USDC[/TD]
[TD]USD[/TD]
[TD]167[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]INRA[/TD]
[TD]INR[/TD]
[TD]30[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]INRB[/TD]
[TD]INR[/TD]
[TD]80[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]INRC[/TD]
[TD]INR[/TD]
[TD]130[/TD]
[TD]180[/TD]
[/TR]
</tbody>[/TABLE]

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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Account[/td][td=bgcolor:#70AD47]Forex[/td][td=bgcolor:#70AD47]Forex Balance[/td][td=bgcolor:#70AD47]GBP Balance[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AUDA[/td][td=bgcolor:#E2EFDA]AUD[/td][td=bgcolor:#E2EFDA]

150​

[/td][td=bgcolor:#E2EFDA]

100​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AUDB[/td][td]AUD[/td][td]

200​

[/td][td]

150​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AUDC[/td][td=bgcolor:#E2EFDA]AUD[/td][td=bgcolor:#E2EFDA]

250​

[/td][td=bgcolor:#E2EFDA]

200​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]USDA[/td][td]USD[/td][td]

67​

[/td][td]

90​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]USDB[/td][td=bgcolor:#E2EFDA]USD[/td][td=bgcolor:#E2EFDA]

117​

[/td][td=bgcolor:#E2EFDA]

140​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]USDC[/td][td]USD[/td][td]

167​

[/td][td]

190​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]INRA[/td][td=bgcolor:#E2EFDA]INR[/td][td=bgcolor:#E2EFDA]

30​

[/td][td=bgcolor:#E2EFDA]

80​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]INRB[/td][td]INR[/td][td]

80​

[/td][td]

130​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]INRC[/td][td=bgcolor:#E2EFDA]INR[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

180​

[/td][/tr]
[/table]

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]

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