# Lookup data from Horizontal set of data

#### ExcelLearnerP

##### New Member
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

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### ExcelLearnerP

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

#### sandy666

##### Banned - Rules violations
with PowerQuery brute force way

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

Code:
``````[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

Last edited:

#### ExcelLearnerP

##### New Member

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

##### Banned - Rules violations
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

##### New Member
Okies. Thanks Sandy!!

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

Wish you a great day

#### sandy666

##### Banned - Rules violations
FYI

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

Replies
12
Views
304
Replies
3
Views
123
Replies
3
Views
240
Replies
18
Views
235
Replies
4
Views
677

1,195,679
Messages
6,011,119
Members
441,585
Latest member
MargaretBartley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back