Vlookup with trailing spaces for the values in the lookup table

[cartherese]

Hi! I was hoping if someone can help with this - I have created a sheet that combines data from 2 different reports. The look up value is saved as number, but when pulling up the two reports, report A saves my lookup data as text without trailing spaces, and report B has my lookup value with 3 trailing spaces behind it.

I was able to pull data from report A by using =vlookup(a1&"", reportA, 3, 0), but I am having trouble running vlookup with report B, because of the 3 trailing spaces after my lookup value. Help! I am trying not to resort to having to update report B and convert the stored value from text to number, any feedback is gladly appreciated!
Thanks!!!

 

[Alex0013]

Hello cartherese,

When you do the &"", just put 3 spaces in it. So =vlookup(a1&" ", reportB, 3, 0). As long as it is consistently 3 spaces, this should work.

Regards,
Alex

 

[Aladin Akyurek]

Try something like...

=VLOOKUP(A1&"*", reportB, 3, 0)

 

[vogel997]

[TABLE="width: 267"]
<tbody>[TR]
[TD]Try this
[/TD]
[/TR]
[TR]
[TD]=vlookup(a1&rept(" ",3), reportB, 3, 0)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[cartherese]

Thank you so all so much for the quick replies! I can finish my report tonight, finally :D

Alex0013, I have tried the &" " (3 spaces)and for the life of me, it just won't take, I even did the len() test to confirm all the values in the lookup table all had 3 trailing spaces, no joy

Aladin and vogel997, both your versions did the trick, thank you sooo much!!!

 

[Aladin Akyurek]

Thank you so all so much for the quick replies! I can finish my report tonight, finally :D

Alex0013, I have tried the &" " (3 spaces)and for the life of me, it just won't take, I even did the len() test to confirm all the values in the lookup table all had 3 trailing spaces, no joy

Aladin and vogel997, both your versions did the trick, thank you sooo much!!!

You are welcome. A note though: The REPT version will fail if the number of the trailing spaces does not equal 3...

 

[cartherese]

You are welcome. A note though: The REPT version will fail if the number of the trailing spaces does not equal 3...

Good info, Aladin, thank you!!!

 

[Aladin Akyurek]

Good info, Aladin, thank you!!!

Glad to help.

 

[Alex0013]

@Aladin, isn't the "*" also dangerous? If the number is contained within another it may return the wrong answer right?

For instance if the lookup value is 998031, and the table has values of 9980314, 9980317, and 998031, it would return the value of the first instance (in this case 9980314) and not the desired value of 998031 right?

Granted if the number of digits is always the same and/or the numbers are different enough where they wouldn't be in each other it wouldn't be an issue.

Just want to double check my logic in thinking this.

Thanks,
Alex

 

[Aladin Akyurek]

@Aladin, isn't the "*" also dangerous? If the number is contained within another it may return the wrong answer right?

Not if the numbers are true numbers, that is, entries of which ISNUMBER would return TRUE.

For instance if the lookup value is 998031, and the table has values of 9980314, 9980317, and 998031, it would return the value of the first instance (in this case 9980314) and not the desired value of 998031 right?

Granted if the number of digits is always the same and/or the numbers are different enough where they wouldn't be in each other it wouldn't be an issue.

Just want to double check my logic in thinking this.

Thanks,
Alex

If the values are text numbers (of which ISNUMBER would be FALSE), you would get indeed undesired results.

If you have to deal with unequally sized text numbers, switch to...

=INDEX(report,MATCH(A1,TRIM(INDEX(repotB,0,1)),0),3)

which must be confirmed with control+shift+enter.