Excel Trying to do a vlookup with a nested right function on the array

[svance]

situation
file one value A
file two value XXXXXA (as part of the array of other data I will need to display in relation to value A)

I want to vlookup value A, in file one, over on file two, and then return the values in columns to the right of it. But, the value A in file two is part of a larger string.

In theory, the vlookup would look something similar to this... (which is obviously not working for me)

-VLOOKUP($A$1,'filetwo(RIGHT(($B$1,1)+0):$Z$10000',2,0) You can see I'm trying to set the range and look at only the right values in the anchoring column

In other words, lookup the value entered in file on A1, got to the range of B1:Z10000, but in B1 apply the RIGHT FUNCTION to only match the last character

Here is a mockup of the files (although in reality file two is a different spread sheet)

 

[Becky]

Hello and welcome to the forum!

Your easiest bet would be to use a wildcard. Try this:

=VLOOKUP("*"&A1,[your range],2,0)

"*"&A1 means any text followed by the value of A1.

Hope this helps!

 

[svance]

[SOLVED] I went with an INDEX MATCH function (that includes a wildcard) So thank you.

 

[Becky]

You're welcome! Glad to hear you got it sorted, thanks for letting me know