How to Use VLookup in an IF Statement
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 utilize the IF statement specifically with the VLookup statement to automatically fill in documents and spreadsheets with data that they generate from user-made forms.
Things You'll Need
- Microsoft Excel
Open Microsoft Excel. Click "File" followed by "New Workbook."
Click "Insert," and then select "Sheet." Click "New Sheet."
Name one sheet "Invoices," and name the other "Accounts." To rename a sheet, right-click on the sheet name and select "Rename."
Populate the columns and rows with random data, but use the boxes in columns A through E.
Familiarize 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.
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.