1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vlookup mismatch between text and no

Discussion in 'Ask an Excel Question' started by xljgd, May 16, 2018 at 4:19 PM.

  1. xljgd

    xljgd Member

    Messages:
    36
    I have a table with columns looking at another table.

    column h
    444 -Task Error
    210
    210
    electrical210
    This looks at my table which the values

    210
    220
    230
    240
    250
    260
    270



    This formula looks at column H and takes out the first 3 letters and compares his value I another table.

    if match is not found it gives me "task error"

    It works out fine when working with numbers but I do have some text in the table. say

    electrical210 which I want he user to see as an Task error and correct it.

    here it is comparing value ele to my tables and not returning any error.

    how do I say if text use one formula if number use the following formula.

    =IF(ISNA(VLOOKUP((LEFT(H5,3))+0,tasklog!$B$9:$B$15,1,FALSE)),"TASK ERROR","")

    Any help will be greatly appreciated.
  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,644
    Maybe,

    =IF(OR(ISERR(0+LEFT(H5,3)),ISNA(VLOOKUP(0+LEFT(H5,3),tasklog!$B$9:$B$15,1,FALSE))),"TASK ERROR","")

    Regards
    Bosco
    xljgd likes this.
  3. xljgd

    xljgd Member

    Messages:
    36
    Works like a charm.
    Thanks for your input and your time.

Share This Page