two vlookups and one hlookup in one formula

[ryan640]

Hi,

I have a table that I am unable to change however I need to make the table set up in a way that I can use to enter into Power BI.

On one sheet I have the months going across the top and I need to make a formula to bring the months amount into rows. I also need them to match two criteria as well.

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]type[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]Account[/TD]
[TD]100[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]type[/TD]
[TD]month[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]Account[/TD]
[TD]Jan 100 (formula)[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]Account[/TD]
[TD]Feb 200 (formula)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

You can unpivot in BI Tools/Power Query or with the pivot table wizard: multiple consolidation range window available in Excel for a while but somewhat hidden on later versions (ALT D P retrieves it).

 

[ryan640]

You can unpivot in BI Tools/Power Query or with the pivot table wizard: multiple consolidation range window available in Excel for a while but somewhat hidden on later versions (ALT D P retrieves it).

I'm not sure it will only be power BI. I need it to be set up without any blanks which is why I thought a formula would be good so then I can hide the sheet.

 

[sheetspread]

Then try the second option:

https://www.youtube.com/watch?v=xmqTN0X-AgY

(you can filter blanks/zeroes in the resulting data table)