+ Reply to Thread
Results 1 to 9 of 9

Result coming back as text not a number.....maybe?

  1. #1
    Registered User
    Join Date
    03-29-2017
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    Result coming back as text not a number.....maybe?

    Hi, first post, so bear with me should it not all be to standard!

    I have a series of codes which end with "PK2", "PK4", etc.
    I need to lookup these against codes in another sheet, without these suffixs, and return data from a corresponding column... so far, so good: (put it on separate lines for sake of easy reading)

    =IF(ISNUMBER(SEARCH("PK24",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK36",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK48",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK16",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK6",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK8",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK10",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK12",A3)),VLOOKUP(LEFT(A3,LEN(A3)-4), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK2",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK3",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC, 28,0),
    IF(ISNUMBER(SEARCH("PK4",A3)),VLOOKUP(LEFT(A3,LEN(A3)-3), TDM!A:CC,28,0),
    IF(VLOOKUP(A3,TDM!A:A,1,0)=A3,VLOOKUP(A3,TDM!A:CC,28,0),0))))))))))))

    This is all working grand, those with all the various "PK"#'s find the right value, and those without also work a charm... except for codes which don't have any letters / symbols, which return #N/A
    (e.g. 900500PK4)

    I have tried formatting the cells as text and numbers, (the two lookup cells and the return destination).
    As a test I added in a random letter it fixes the problem, but this does little more than prove I know what the problem revolves around, but not how to fix it.

    Sure after all this it is fairly easy, but I'm stumped!!

    Cheers

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Result coming back as text not a number.....maybe?

    The formula will return an error if your vlookup is running off of a search that was unsuccessful. It's not clear what you're desired outcome is. If you want to remove the error and replace it with something else, simply add =IFERROR(your full equation,then whatever you want it to return if there is no match). For example, =IFERROR(formula,"") will return blanks when there is an error.

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Result coming back as text not a number.....maybe?

    Welcome to the forum.
    You can upload file for the member to look at.


    To attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Nice to have you with us.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Result coming back as text not a number.....maybe?

    Looks like the rule is:

    If A3 contains a specific "PK" number use the value that is to the left of the PK in A3, look it up in the TDM table and extract the value in column AB,
    otherwise find the entire A3 value in the TDM table again extracting column AB.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Result coming back as text not a number.....maybe?

    Maybe try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Original formula could be just this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Result coming back as text not a number.....maybe?

    Quote Originally Posted by TMS View Post
    Original formula could be just this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That's not necessarily correct.
    If A3 is ABCDEPK55 your above formula will perform a VLOOKUP on ABCDE.
    But PK55 is not one of the numbers in the original formula, therefore the entire string will be searched for.
    It may be the OP is checking for specific PK numbers of which 55 is not one of them.

    UPDATE: Ah! Just seen the word "could" in your reply
    Last edited by Special-K; 03-29-2017 at 10:51 AM.

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Result coming back as text not a number.....maybe?

    the OP i think needs specific number of characters based on PK##(len 4) or PK#(len 3)

  8. #8
    Registered User
    Join Date
    03-29-2017
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    Re: Result coming back as text not a number.....maybe?

    Damn, you guys don't hang about! Thanks for the amazingly speedy answers!

    TMS you seem to have knocked the nail on the head... will now retreat back to my cave and try to work out what the formula is actually doing.

    Thanks again

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Result coming back as text not a number.....maybe?

    It may be the OP is checking for specific PK numbers of which 55 is not one of them.

    UPDATE: Ah! Just seen the word "could" in your reply
    the OP i think needs specific number of characters based on PK##(len 4) or PK#(len 3)
    To be fair, I did kind of make the assumption that these were known, current, specific PK codes and, consequently, the formula could grow if more codes were added. On that basis, it wouldn't really be scaleable. I would be inclined to list all valid PK codes in a separate configuration sheet and define a Dynamic Named Range to facilitate lookup. In that way, the valid/invalid PK codes could be checked and, for the valid ones, you can then use the shorter formula.

    and try to work out what the formula is actually doing.
    Essentially, it is duplicating the formula, hence the desire to shorten it. The first version will check for PK codes with an alpahnumeric "prefix". If it doesn't find anything, it will try again by adding a zero to the numeric "prefix", this converting it from a text value (from the LEFT function) to a numeric value. In my simple testing, that works. In other, more complex scenarios, it may not.

    An alternative approach would be to convert the lookup table, column A, to Text (rather than a mix of alphanumeric and pure numeric values).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. HELP! I have a VBA pop up window that keeps coming back
    By mat83400 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2016, 06:14 AM
  2. [SOLVED] IF AND Functions coming back as False
    By Tordah in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-03-2015, 05:22 PM
  3. Replies: 7
    Last Post: 05-15-2015, 11:57 AM
  4. combobox result coming as text, not numeric
    By jalba in forum Excel General
    Replies: 14
    Last Post: 06-06-2014, 09:08 PM
  5. IF contain # in a text extract the full number coming after it
    By nanda123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-13-2013, 06:49 AM
  6. VLOOKUP Formula coming back with NA#
    By monza2 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-18-2012, 01:38 AM
  7. Replies: 1
    Last Post: 06-03-2005, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1