Look Up values...

[blownbyyou]

So I am sitting here racking my brain trying to figure this out and cant for the life of me. Was wondering if I could get some help. I am trying to do a look up value in the table below. If you see on the left table how items repeat themselves but there is only one value in result. I am trying to find a way to move that information into the destination table ignoring blanks....Any help?



[TABLE="width: 354"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 2"]Source Table[/TD]
[TD][/TD]
[TD="colspan: 2"]Destination Table[/TD]
[/TR]
[TR]
[TD]Item#[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD]Search[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD] [/TD]
[TD][/TD]
[TD]a[/TD]
[TD]aa[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD] [/TD]
[TD][/TD]
[TD]b[/TD]
[TD]aa[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]ab[/TD]
[TD][/TD]
[TD]c[/TD]
[TD]ab[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD] [/TD]
[TD][/TD]
[TD]d[/TD]
[TD]ac[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]aa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]ac[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]aa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joyner]

Hello, there may be easier ways. but here is one way if it is as you lay it out - in the second column blanks and text values. This is an array formula entered with Control Shift and Enter, not just enter:

=INDEX($B$3:$B$13,MATCH(1,(D3=$A$3:$A$13)*ISTEXT($B$3:$B$13),0))

Data starting in A3 and the lookup values (a,b,c,d), starting in cell D3

 

[blownbyyou]

Hello, there may be easier ways. but here is one way if it is as you lay it out - in the second column blanks and text values. This is an array formula entered with Control Shift and Enter, not just enter:

=INDEX($B$3:$B$13,MATCH(1,(D3=$A$3:$A$13)*ISTEXT($B$3:$B$13),0))

Data starting in A3 and the lookup values (a,b,c,d), starting in cell D3

Thanks,Idon'thavemyworkcomputerwithme,butiwillcheckitouttomorrow.....noideawhymyspacebarisn'twork...

 

[blownbyyou]

So unfortunately Power Pivot doesn't allow the use of index....

any other suggestions?

 

[Eric W]

My apologies. I didn't notice the forum until too late. I'm don't have access to Power BI, so I can't really help. You may want to open a new thread so you get new eyes on it.


I've also noticed the space bar issue before, too. It seems to be a forum glitch. I can usually get around it by clicking "Go Advanced" and using that input screen.

 

[citizenbh]

Load table in Power Query, and Filter Result Column (disable null value).

 

[krrishkrsna]

below steps might solve your problem

1. convert data Source and Destination to Table format.
2. add two tables to Power Pivot.
3. in Power Pivot, click on Diagram View in menu Home.
4. create relationship between two tables as taking Item column
5. in source table, type formula =RELATED(DestTbl[Result])