Good Morning,
I am using the vlookup function to pull in values from another sheet that I will call sheet2. I am using a cell reference for the lookup value on sheet1. On sheet1, the value in the reference cell has a superscript "1" that corresponds to a footnote at the bottom of the page. The corresponding lookup value on sheet2 does not have any superscript. Is it possible to make the vlookup function ignore the superscript for the purpose of the lookup?
maybe try...
=Vlookup(Left(A1,Len(A1)-1),.....
where A1 contains the superscript....
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Except if some of them contains superscript.. and some of them dont...
jersey can you post example here?
Sure. This is what the first column looks like along with the footnotes.
Oops. Here it is in non-2007 format.
To take into account that some may not have those superscripts... then try something like:Originally Posted by jerseyguy1996
Please Login or Register to view this content.
As I understand supercript is in the table so it's a bit complicated.
Don't know how to step into table array and check is there superscript.
If superscript can't be defined on some other way I would instert one more column and remove last number....
=IF(ISNUMBER(RIGHT(A1;1)*1);LEFT(A1;LEN(A1)-1);A1)
An then define lookup table.
Last edited by zbor; 03-16-2009 at 05:34 PM.
That works great except when the mutual fund name happens to end in a number (i.e. Fidelity Freedom 2010). I'm thinking first check if it returns an error, and if not just do the normal vlookup and if it does then go through the process of taking out the right most character.
Good idea:
Try
Please Login or Register to view this content.
I ended up doing this which seems to work out pretty well.
I've never used the LEFT and LEN functions. I appreciate the help very much!!!Please Login or Register to view this content.
Mine will return a blank instead of #NA if none of the 2 options are matched...
In my case I have a few other conditionals being tested in that cell so the shorter that part of the formula is, the better. My final formula ended up looking like this:
I really appreciate your help in setting me on the right path.Please Login or Register to view this content.
Last edited by jerseyguy1996; 03-16-2009 at 12:42 PM.
It might just be easier for you to make the 1 at the end of the string have the same color as the background so it is there for the vlookup, but it can't be seen.
But then I wouldn't be able to impress everyone in my office with my formula writing prowess...LOL
Okay in all seriousness....what you just suggested is a really great idea that I think may be the best bet.
Why not just put the footnote references in a separate column and skip all the complexity?
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks