Excel Magic Trick 558: VLOOKUP to verify if Whole Record is in Table




About this tutorial:

Video duration: 6:56
Download Files:

See how to check if a record is in a table using VLOOKUP. See how to do this for a 2 column (field) table and a 4 column (field) table. See how to add a concatenated column and concatenate lookup_value in VLOOKUP function.
Match. Is record in table? Is item in list? Helper column to speed up calculations.

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

7 thoughts on “Excel Magic Trick 558: VLOOKUP to verify if Whole Record is in Table

    ExcelIsFun

    (January 14, 2020 - 7:31 am)

    What is your question?

    [MasterFile.xlsx]Cust_Lis­t!A2

    has to turn to this:

    'C:UsersVickyAppDataR­oamingMicrosoftExcel[Master­File.xlsx]Cust_List'!A2

    or else it would not know where to look!

    The two workbooks are connected and communicating with each other because the file knows where it is saved!

    vicky shah

    (January 14, 2020 - 7:31 am)

    I have a master file and a get data file by the name of sales indents.
    Now when i pulled the data from the master file. i put the command as :
    "=IF([MasterFile.xlsx]Cust_List!A2=" "," ",[MasterFile.xlsx]Cust_List!A2)"
    "I got the data perfect.
    But when i saved and closed the sheet, I was shocked to find out this.
    "=IF('C:UsersVickyAppDataRoamingMicrosoftExcel[MasterFile.xlsx]Cust_List'!A2=" "," ",'C:UsersVickyAppDataRoamingMicrosoftExcel[MasterFile.xlsx]Cust_List'!A2)"

    ExcelIsFun

    (January 14, 2020 - 7:31 am)

    I have a playlist with a few videos about determining whether or not an item is in another list. Search for this playlist title:

    'Excel Compare / Merge Two Lists'

    Then click on the playlist in the list of returned links.

    ExcelIsFun

    (January 14, 2020 - 7:31 am)

    I do not understand your question.

    ExcelIsFun

    (January 14, 2020 - 7:31 am)

    Sure!

    Saif Al Baloushi

    (January 14, 2020 - 7:31 am)

    How about using SUMPRODUCT , IF formulas for example:
    =IF(SUMPRODUCT((A1:A3=A6)*(B1:B3=B6)*(C1:C3=C6))>0;"True";"False")
    when the table in A1:C3 and testing cells in A6:C6

    ExcelIsFun

    (January 14, 2020 - 7:31 am)

    You are welcome!

    Clutch!! I love that word!

Leave a Reply

Your email address will not be published. Required fields are marked *