Using IF function with VLOOKUP for VBA
Results 1 to 2 of 2

Thread: Using IF function with VLOOKUP for VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Using IF function with VLOOKUP for VBA

    I currently have an excel field using a formula and data validation. The formula is an IF statement that looks for any text from one cell(ie equipment part number) and then applies a VLOOKUP to find the name of the equipment. Based on the output of the VLOOKUP there is a data validation for a list to select either New or Used + Equipment type. All this is happening inside a table and I am having issues with the IF/VLOOKUP formula not auto-copying to a new row when new information is added. The data validation copies down to the new table row but not the formula. I am wanting to create a macro that applies to the entire Column that executes the formula function.

    example of the formula
    =IF(J2="","",VLOOKUP(J2,Parts!$A:$B,2,FALSE))

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,607
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Using IF function with VLOOKUP for VBA

    The formula does copy down inside a table

    Use this notation (PartNo is the header in column J)

    =IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    A B C D E F G H I PartNo Type formula in column K
    2
    20/07/2019
    54
    70
    30
    81
    17
    51
    31
    15
    Part001 a =IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))
    3
    20/07/2019
    10
    42
    16
    71
    92
    27
    88
    72
    Part002 b =IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))
    Sheet: Sheet1

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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