Results 1 to 6 of 6

Thread: Vlookup

  1. #1
    Seeker Luigi802's Avatar
    Join Date
    Aug 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Unhappy Vlookup



    Register for a FREE account, and/
    or Log in to avoid these ads!

    So if you look at the attached sheet, I want to make some kind of VLOOKUP formula or any formula what would enter the correct category/sub category on the DATA sheet based on if the contents the the description "contain" data in the description column on AutoCat sheet....for example on line 15 on the DATA sheet, it says "BLUE RIDGE COMM CABLE BILL ***********2001" and on the AutoCat Sheet on line 3 it just says Blue Ridge, how could I make the category and sub category automatically fill in on the DATA sheet based on the data in the Auto Cat sheet?
    Attached Files Attached Files

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    If you do not have duplicates in the worksheet 'data' in column C, use the following formulas
    Formula in D4 cell, copy down
    Code:
    =VLOOKUP([@Description];T_Auto_Cat;2;FALSE)
    Formula in E4 cell, copy down
    Code:
    =VLOOKUP([@Description];T_Auto_Cat;3;FALSE)

  3. #3
    Seeker Luigi802's Avatar
    Join Date
    Aug 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by navic View Post
    If you do not have duplicates in the worksheet 'data' in column C, use the following formulas
    Formula in D4 cell, copy down
    Code:
    =VLOOKUP([@Description];T_Auto_Cat;2;FALSE)
    Formula in E4 cell, copy down
    Code:
    =VLOOKUP([@Description];T_Auto_Cat;3;FALSE)

    I keep getting the "there is a problem with your formula" popup, I thought it was because of the ; in stead of a , so changed it to a , and then I got #N/A in every column except if it was an exact match

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    My apology, I did not read the question properly.
    Try the following ARRAY formula
    In D4 cell try (copy across D and E column)
    Code:
    =IFERROR(INDEX(AutoCat!C$3:C$30;SMALL(IF(ISNUMBER(SEARCH(" "&AutoCat!$B$3:$B$30&" ";" "&$C4&" "));ROW(AutoCat!$B$3:$B$30)-MIN(ROW(AutoCat!$B$3:$B$30))+1;"");COLUMN($A1)));"")
    Attached Files Attached Files

  5. #5
    Seeker Luigi802's Avatar
    Join Date
    Aug 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    AWESOME! Thank you so much, that works perfectly!

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    You're welcome
    On this link also, you have the tutorial for the reverse situation.

    - Return the data if the word with the sentence text matches

Tags for this Thread

Posting Permissions

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