Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Multiple Dual-Validations COUNTA
  • 0% interest promotion Basic Math
  • CountIf limitation COUNTIF
  • Looplist – Repeating months using formulas Basic Math
  • Limited Ceiling Basic Math
  • Schedule column Basic Math
  • Pixel Excel Drawing Format Cells
  • Hello world! Uncategorized

VLookup

Posted on September 26, 2016 By ANmar

I have been asked for a while now to just put a simple VLookup function, here it is, I know this might be stupid to post it here, but we need to fill requests from all types of visitors.

Vlookup is the most famous function in Excel, some people actually refer to Excel as VLookup.
Even though I do not like that fact, especially when we have other powerful functions like INDEX, OFFSET, INDIRECT and others.

So, what is VLOOKUP?

VLookup is going to search for an item in the left column of your table (or selection), as in the screenshot below, then bring me back the value in the same row where it find that item from another column.

 

Post1499

The Green box (Lookup_Value) is what the function will search for, and the vertical green box is where, this is because we selected the range (Table_Array) in Blue as the area of D:G, so VLookup will search in D, this is the V part in VLOOKUP, we should also expect to see HLookup.
Means if item is not found in column D, VLookup will return as #N/A error

The Red number (COl_Index_Num) is the number of column we need to have result from, again, because we selected D:G, if we types 1 in Col_Index_Num, we will get what is in column D that matches the same row where we found “Mar”.

In this case “Mar”

Post1500

 

While the “Range_Lookup” is something related to the type of search, we just did “False” to make sure it searches for the exact match. to read more about “Range_Lookup”, please check the ‘Help’ section in Excel

The idea is to get result from column other than 1, this is why we are doing the search right?

So our function will be

Sample:

=VLOOKUP("Mar", D:G, 4, False)

Post1501

And the result is as we expected.

 

Other ways to do Vlookup is using INDEX + MATCH or OFFSET + MATCH

 

Lookup and References, Standard functions, VLOOKUP

Post navigation

Previous Post: CONVERT function
Next Post: Links between two workbooks – dynamically using functions

Related Posts

  • Fx to cut long column into two columns Formulas - combined functions
  • Search across sheets in function ADDRESS
  • Cuts string of list of items Formulas - combined functions
  • Multiple lines in cell using functions CHAR
  • Extract Address into table Formulas - combined functions
  • GoLast to jump to last cell in a column CELL

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Blank (isempty) VS “” (null string) Excel User tips
  • Multiple-Validations (connected to each other) Data Validation
  • Validation based on another Validation COUNTA
  • Count cells with condition – multiple conditions COUNTIFS
  • Rotate table using 1 formula ADDRESS
  • Calculate hours between two cells ABS
  • CHOOSE – tree of decisions CHOOSE
  • Search across sheets in function ADDRESS

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme