Lookup not returning a value

[mutatrt]

I want to look up a material number and have it return a material description - 

Current Formula :Text(LookUp(TABLE,MATERIALNUMBER=MATERIALNUMBERINPUT.Text,MATERIALDESCRIPTION))

The MaterialNumberInput is formatted as a text, the table column that has the material numbers is also set as a text. This is not giving me any errors it is just returning blank. I've tried to refresh and reload my data, it worked once when I refreshed for 5 minutes then it started returning blank again.

I've also tried this formula: First(Filter(TABLE,MATERIALNUMBER=MATERIALNUMBERINPUT.Text)).MATERIALDESCRIPTION 

This gives me the same result, just a blank text box..

Any ideas on how I can make this work? I've used this type of lookup before on other apps to do the same thing and it works.

[mutatrt]

Picture of app screen

[calerof]

Hi @mutatrt,

Change your formula like this:

LookUp(TABLE,MATERIALNUMBER=MATERIALNUMBERINPUT.Text).MATERIALDESCRIPTION

Cheers,

Fernando

[TheRobRush]

just cause sometimes things are weird try this

LookUp(
       TABLE,
       MATERIALNUMBER=Trim(MATERIALNUMBERINPUT.Text),
       MATERIALDESCRIPTION
       )

and if that is a no go try

First(Filter(AddColumns(TABLE, "formattedNum", Lower(Trim(MATERIALNUMBER))),
       "formattedNum"=Lower(Trim(MATERIALNUMBERINPUT.Text))).MATERIALDESCRIPTION

see if either give you what you are looking for.

Just curious though, why did you go with a text column, are the material numbers sometimes alphanumeric?

[TheRobRush]

@calerof the lookup formula can be written both 

LookUp(TABLE,MATERIALNUMBER=MATERIALNUMBERINPUT.Text,MATERIALDESCRIPTION)

and 

LookUp(TABLE,MATERIALNUMBER=MATERIALNUMBERINPUT.Text).MATERIALDESCRIPTION

just a matter of preference, so his original syntax was correct, something else is issue

[calerof]

@mutatrt,

If this answer responded your question please mark it Accept as Solution so others can find it.

[mutatrt]

Tried both of your suggestions - still returning blank.

For your question on why I chose text column, that is just what I've had the best luck with Lookup functions - just to try it I did switch the input and column to number a format with each of the solutions provided in your post and above to see if that maybe was what was causing the blank return but unfortunately they all returned a blank value.

I also did check my material number column to make sure there were no spaces before or after the material number.

[TheRobRush]

Are any of these numbers duplicated by chance in the (I am assuming it is) SharePoint List? My next step to suggest would be place this in a label 

CountRows(Filter(AddColumns(TABLE, "formattedNum", Lower(Trim(MATERIALNUMBER))),
       "formattedNum"=Lower(Trim(MATERIALNUMBERINPUT.Text)))

see if it is also returning a result of 0 like your lookup, if it is returning anything above 0 

place a vertical gallery in your app with a items property of 

Filter(AddColumns(TABLE, "formattedNum", Lower(Trim(MATERIALNUMBER))),
       "formattedNum"=Lower(Trim(MATERIALNUMBERINPUT.Text))

and place a single label in it with the property MATERIALDESCRIPTION

and see what is showing in this gallery. 

If all of these show nothing, take a chance and go to your datasource menu, remove this datasource, wait for all the formulas to break, then re add it back on

[mutatrt]

CountRows(Filter(AddColumns(TABLE, "formattedNum", Lower(Trim(MATERIALNUMBER))),
       "formattedNum"=Lower(Trim(MATERIALNUMBERINPUT.Text)))

Did the above and received a "0" for the value

It is not a sharepoint list, but a table in excel connected through OneDrive, not sure if that would matter on the issues I am seeing. 

I also removed the data source and added back in - I will continue to play around with this to see if I can find a solution - thank you for your help this far. 

[TheRobRush]

Ah well, I won't be able to give you much guidance then. Excel is a really bad datasource for PowerApps and often does not work as expected. Hope you figure it out, but would highly recommend swapping over to SharePoint