vlook up & if isnumber issue

  • Hi guys hope you are all well,


    I have a columns witch consist of numbers and letters ie column A1 value is 2c.


    I want to be able to count the numbers and ignore the letter.


    I been trying to adjust my current formula to fit a if isnumer, with no luck :/


    my formula is: =IFERROR(VLOOKUP($D$14,Table57,3,FALSE),"")


    how do I adjust it to accommodate the if isnumber part?


    Thanks in advance !

  • Re: vlook up & if isnumber issue


    Quote from azumi;736541

    Maybe:


    =IFERROR(VLOOKUP(LEFT($D$14,1),Table57,3,FALSE),"")


    thanks will try this one out.

  • Re: vlook up & if isnumber issue


    Quote from Windy58;736542

    Is there only one number preceding the text?


    Windy


    Hi, yes its one number and one letter.

  • Re: vlook up & if isnumber issue


    Quote from azumi;736541

    Maybe:


    =IFERROR(VLOOKUP(LEFT($D$14,1),Table57,3,FALSE),"")



    Just tried it, not getting anything show up in the columns just blank.

  • Re: vlook up & if isnumber issue


    You might need to coerce the LEFT() result to a number:


    [COLOR="#0000FF"]=IFERROR(VLOOKUP(LEFT($D$14,1)[COLOR="#FF0000"]+0[/COLOR],Table57,3,FALSE),"") [/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: vlook up & if isnumber issue


    Quote from NBVC;736547

    You might need to coerce the LEFT() result to a number:


    =IFERROR(VLOOKUP(LEFT($D$14,1)+0,Table57,3,FALSE),"")


    Hi, thanks. just tried the above and is returning a blank.

  • Re: vlook up & if isnumber issue


    Are you sure the first column of table Table57 contains single digit numbers?


    If yes, post a sample workbook.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: vlook up & if isnumber issue


    Quote from NBVC;736567

    Are you sure the first column of table Table57 contains single digit numbers?


    If yes, post a sample workbook.


    Sorry my bad. Its column d in table 57 which contains a digit and a letter. The table starts from column B and ends at column P.


    The code will be entered in a separate worksheet in column G iirc


    I'm back in the office Monday so can confirm the above then

  • Re: vlook up & if isnumber issue


    You will have to make sure that your reference table in the VLOOKUP starts at column D then...


    Or, alternatively, you can try INDEX/MATCH


    e.g.


    [COLOR="#0000FF"]=IFERROR(INDEX(INDEX(Table57,0,[COLOR="#0000FF"][COLOR="#FF0000"]3[/COLOR][/COLOR]),MATCH(LEFT($D$14,1)+0,INDEX(Table57,0,[COLOR="#FF0000"]4[/COLOR]),0)),"")[/COLOR]


    The Red 3 represents column D (which is third column in the table57) and the the 4 assumes you want to pull from column E (the fourth column - change to suit)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: vlook up & if isnumber issue



    Morning,


    Just tried the above but doesn't seem to want to work for me ...


    iv added a link to my workbook, could you let me know where im going wrong.


    Many Thanks


    https://www.dropbox.com/s/g353fsj1f2lufk1/test.xlsm?dl=0


    The formula is in the Dashboard worksheet in column F15,F17 and F19

  • Re: vlook up & if isnumber issue


    From the start you mentioned your were looking up part of a cell, this ccm01 is matching the whole text string....


    Try:


    [COLOR="#0000FF"]=IFERROR(INDEX(INDEX(Table57,0,3),MATCH(D14,INDEX(Table57,0,1),0)),"")[/COLOR]


    or
    [COLOR="#0000FF"]
    =IFERROR(VLOOKUP(D14,Table57,3,0),"")[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: vlook up & if isnumber issue


    Thanks for getting back to me.


    =IFERROR(INDEX(INDEX(Table57,0,3),MATCH(D14,INDEX(Table57,0,1),0)),"")


    the above works, the issue im having is not being able to reflect the number in the respective graphs.


    I think this was due to there being a letter in the cell.


    any ideas?


    Thanks again

  • Re: vlook up & if isnumber issue


    Ok. I understand now. You want the number part of the result... not to lookup a number part of a mixed string.....


    Try then:


    [COLOR="#0000FF"]=IFERROR(LEFT(INDEX(INDEX(Table57,0,3),MATCH(D14,INDEX(Table57,0,1),0)),1)+0,"")[/COLOR]


    or


    [COLOR="#0000FF"]=IFERROR(LEFT(VLOOKUP(D14,Table57,3,0),1)+0,"")[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: vlook up & if isnumber issue



    Both of the above are returning back "FALSE"

  • Re: vlook up & if isnumber issue


    I've tried both of them again, and they both return a 2.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: vlook up & if isnumber issue


    Hi, Thanks it seems to have sorted it self out :/


    appreciate all the help.


    Is there a way to show both the number and letter but only count the number?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!