How to Use VLookup in an IF Statement

Written by suman medda
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use VLookup in an IF Statement
The IF statement is an extremely useful tool in coding. (Jupiterimages/Photos.com/Getty Images)

Using the IF statement adds tremendous power to VLookup. An IF statement will function until the instructions inside the IF block are met. Should conditions not be met within the IF statement, the IF statement will terminate unsuccessfully. This adds looping capabilities and flow control to Excel and VLookup results. Excel programmers utilise the IF statement specifically with the VLookup statement to automatically fill in documents and spreadsheets with data that they generate from user-made forms.

Skill level:
Moderately Challenging

Other People Are Reading

Things you need

  • Microsoft Excel

Show MoreHide

Instructions

  1. 1

    Open Microsoft Excel. Click "File" followed by "New Workbook."

  2. 2

    Click "Insert," and then select "Sheet." Click "New Sheet."

  3. 3

    Name one sheet "Invoices," and name the other "Accounts." To rename a sheet, right-click on the sheet name and select "Rename."

  4. 4

    Populate the columns and rows with random data, but use the boxes in columns A through E.

  5. 5

    Familiarise yourself with the VLookup command: VLookup( value, table_array, index_number, not_exact_match). Replace "value" with the number that you are searching for in the first column of your array. Replace "table_array" with two or more columns that are vertically sorted. Replace "index_number" with the column number within the "table_array" to be searched. Replace "not_exact_match" with "0" or "false" to signal a search for an exact match. Otherwise, set it to "1" or "true" for the closest result that is larger than the specified value.

  6. 6

    Combine the IF statement with a VLookup to yield:

    "=IF (ISNA(VLOOKUP(4,A2:D10,2,FALSE)) = TRUE, "Entity not found")." Replace "4" with the number being searched for. Replace "A2:D10" with the range to be searched. Replace "2" with the column to search in. Replace "FALSE" with "TRUE" if you would like a non-exact search. Replace " = TRUE" with "FALSE" if you want a not found message, and replace "Entity not found" with any message you want to display.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.