NKW6 Pt 2 VLOOKUP

Part 2 VLOOKUP

In this section I examine the fundamentals of VLOOKUP. 

So what is VLOOKUP?  Well it is a way of searching a table for information based on a reference value and then returning the content from a specified column.  We can use this to search a reference table for example a list of clients in a database or we can use it to auto populate a form based on a reference.  The key aspect here is that there needs to be a unique reference identifier and the column containing the identifier needs to be the first column.

 

Let’s see what this looks like when written out:

·         =VLOOKUP(P1,A1:N5000,5,0)

·         The =VLOOKUP tells the programme what function you are using

·         All functions are enclosed in brackets ()

·         P1 is the lookup value (this is what the programme will seek out from the table.  In this case it is a cell reference but could easily be some text or a figure

·         The A1:N5000 is the table array or range where the programme will look.  Column 1 of this range needs to contain the lookup value

·         The 5 is the column number that the programme will look to when returning a value

·          0 indicates that the programme should return a value when an exact match is made.  If 1 was indicated then an approximate match can be made.  You must be careful when using this as incorrect values can be returned when you use 1

As with most functions you can substitute elements for references to other cells.  In this case the lookup value has been referenced to a cell.  The table array is a relative reference but could be named or absolute.  The column number can also be replaced with a reference or calculation but be aware that if the column number exceeds the number of columns in the table array then an error message will appear.  The final element approximate (1) or Exact (0) cannot be changed or the cell must contain either of the two options.

As before it is good to get to grips with an example so check out the video below:




Comments

Popular posts from this blog

Conditional Formatting

Power Query: Merge and Append

NKW6 PT1 - IF