Excel Code Query

The_Hurricane

Hi All,

What does the following piece of code mean? I understand it so far as it's matching the value in the current tab with the value in tab "Tab Name" cell A and returning what is in cell C. However I'm confused as to what the "3" and "FALSE" relate to, any help would be great?

=VLOOKUP(W5,'Tab Name'!A:C,3,FALSE)

Thanks

bod1467

VLOOKUP is a function that allows you to search a table/array for a value, then return an associated cell value.

W5 = the cell reference for what you are looking for
'Tab Name'!A:C = the table/array - in this case all rows of columns A to C are selected (W5 is looked for in column 1 ... A in this case)
3 = the 3rd column of that array ... C in this case
FALSE = only return a value if there is an exact match in column 1, else return #N/A. TRUE would mean return a value if there is a close match.

Let's say W5 = "Joe" and "Joe" is contained in cell A10, the function would return the value of cell C10.

VLOOKUP can be a very powerful tool - I use it quite a lot.

The_Hurricane

bod1467 wrote: »

VLOOKUP is a function that allows you to search a table/array for a value, then return an associated cell value.

W5 = the cell reference for what you are looking for
'Tab Name'!A:C = the table/array - in this case all rows of columns A to C are selected (W5 is looked for in column 1 ... A in this case)
3 = the 3rd column of that array ... C in this case
FALSE = only return a value if there is an exact match in column 1, else return #N/A. TRUE would mean return a value if there is a close match.

Let's say W5 = "Joe" and "Joe" is contained in cell A10, the function would return the value of cell C10.

Brilliant that makes sense, if the table had four columns then I presume the "3" would be a "4"?

bod1467

Yes - if it was the 4th column entry you wanted to find. It could still be 3 too.

The number can be anywhere between 1 (although that would be pointless) and the highest column number in the table/array. It all depends on which column of data you want to return info from.

InsideInsurance

The_Hurricane wrote: »

Brilliant that makes sense, if the table had four columns then I presume the "3" would be a "4"?

Not exactly, the number is simply the column that you want it returned from so if it had 20 columns but you only wanted the data from column 3 you'd still enter 3 as the column number even though the table has more columns.

Also remember vlookup only returns the first row it finds with a match. If there are multiple matches it'll return the first in the order they are listed into the table.

The_Hurricane

InsideInsurance wrote: »

Not exactly, the number is simply the column that you want it returned from so if it had 20 columns but you only wanted the data from column 3 you'd still enter 3 as the column number even though the table has more columns.

Also remember vlookup only returns the first row it finds with a match. If there are multiple matches it'll return the first in the order they are listed into the table.

That second point was my next question; is there a way around multiple entries?

InsideInsurance

Not using vlookup, you need to use an array function for that sort of thing. See http://office.microsoft.com/en-gb/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx for examples

It also depends on what you are really trying to do. Spreadsheets and their pivot tables are powerful things but there is a limit to their intended scope and you may be looking at things more suited to a database if you are wanting to bring back multiple rows.