Index Match Based on a formulated value

[boll55]

I really enjoy using index match for look up purposes, i find it very useful but i am struggling with this one instance. I have a formula in column T that reads =LEFT(U7, LEN(U7)-1) and generates a number for me. I want to Index match off of this number but it is not working and only returning N/A. I know my index match formula is correct but the only thing i can assume is it cant generate off this formula but i generate off of formulas all the time so idk why this is an issue this time. Please help!!

Thanks!

 

[njimack]

LEFT returns a TEXT value. To convert to an actual value, try one of the following
=LEFT(U7, LEN(U7)-1)+0
=LEFT(U7, LEN(U7)-1)*1
--LEFT(U7, LEN(U7)-1)

 

[Jonmo1]

The Left function (as well as Right and Mid) returns a TEXT String, not a number.
Even if the result is numerical characters.

Try adding 0 to the end of the left function..
=LEFT(U7, LEN(U7)-1)+0

Note, this will return an error if the result of the left function is NOT numerical characters.

 

[Norie]

Your formula returns a string, so if you are trying to match result against numeric values it won't work.

You need to convert the result of the formula to a number.

Here's one way to do it.

=LEFT(U7-LEN(U7)-1)+0

 

[boll55]

Amazing! Thank you all!

 

[Jonmo1]

Glad to help, thaks for the feedback.