VLOOKUP Help please
 

I guess I'm a bit thick because I am just not getting it after looking at
help and searching the forum here...
I have a separate worksheet that has 2 columns, an item number and a
description. I would like to NOT have to enter the description each time in
separate worksheets so I thought VLOOKUP would be the way to go.
I have accounts in different tabs (worksheets) in a workbook and have
created a tab (worksheet) with the item number and description only: Items is
the name of the worksheet and columns A2 to A200 have the number and B2 to
B200 the corresponding description.
Something I'm doing is wrong because this isn't working:
VLOOKUP(Items!a2:a200,Description,False)
Could someone please help out?
Thanks in advance!!!

You are using the VLOOKUP-function incorrectly:

It should be something like this:
=Vlookup
(ToBeLookedUpValue,RangeContainingValue,ColumnOfRa ngeContainingToBeFoundI
tem,False)

Which would be something like:
Range A1 contains value 100
Range A2 to A200 in worksheet "Items" contain numbers from 1 to 199 by
example), including "100".
Range B2 to B200 in worksheet "Items" contain the descriptions for items
1 to 199

To get the description for value "100", do this:

=Vlookup(A1,Items!A2:A200,2,False)

HTH,
CoRrRan

"?B?Sm9oblQ=?=" wrote in
:

I guess I'm a bit thick because I am just not getting it after looking
at help and searching the forum here...
I have a separate worksheet that has 2 columns, an item number and a
description. I would like to NOT have to enter the description each
time in separate worksheets so I thought VLOOKUP would be the way to
go. I have accounts in different tabs (worksheets) in a workbook and
have created a tab (worksheet) with the item number and description
only: Items is the name of the worksheet and columns A2 to A200 have
the number and B2 to B200 the corresponding description.
Something I'm doing is wrong because this isn't working:
VLOOKUP(Items!a2:a200,Description,False)
Could someone please help out?
Thanks in advance!!!

It's easy to get lost in a formula if you don't really understand what the
formula is actually
doing:-

If you just want the correct syntax then you can stop here, else read past
the formula

Assuming you are in any cell (Except A1) on any sheet except Items, and the
data you want to match is in cell A1 on this sheet:-
VLOOKUP(A1,Items!A2:B200,2,FALSE)


Syntax
=VLOOKUP(lookup_value, table_array , col_index_num, range_lookup)
=VLOOKUP( A1 , Items!A2:B200 , 2 ,
FALSE )


The VLOOKUP function essentially takes a value that you specify, whether it
be a hardcoded
number/letter/text or a value within a cell reference, and then goes and
looks it up in a table.
It will look for that value in the leftmost column of the table, and either
find it or the closest
match, and will then return the corresponding value on the same row, in
whatever column of that
table that you tell it to:-

Example - With the following table

D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40

and with your value that you are looking up in say cell A1 (and let's assume
it is 25 for
example).

You can put a formula in pretty much any other cell, that says, take the
value in cell A1 (25), go
and look for it in the lefthand column of the table (D1:D5), and then when
you have found it (or
the lowest closest number to it), go to the second column (or whichever one
you specify if there
are more than 2) and give me the number that it is on the same row in that
column.

So, with the formula being =VLOOKUP(A1,D1:E5,2) it will first take the
value in A1 which is 25,
then go look for it in the leftmost column (D) of your specified table
(D1:E5), and it will try to
find that number. Now it isn't there, so what it will do is look for the
next lower closest
number, which in this case will be 20, and the 2 in the formula says to go
and get the value in
the 2nd column (E) in your table, that is on the same row as the 20. That
value in this case is
0.3

If you put the value 30 or 31 or 32 etc into A1 now, you will see the result
of the formula
change, because now it will either find those numbers or the lower closest
number (and in each
case there it is 30), and will subsequently return 0.35 as the corresponding
value.

Just to show you how the 2 really works in that formula, if you added one
more column to your
table so that it looked like this:-

D E F
1 1 0.20 0.15
2 10 0.25 0.25
3 20 0.30 0.35
4 30 0.35 0.45
5 40 0.40 0.55

and you actually wanted the value from Col F, then you would simpl;y change
the 2 in the formula
to a 3 to signify the third column, eg:-

=VLOOKUP(A1,D1:E5,3)

With the examples already given, 25 in A1 would return 0.35, and 30/31/32
would return 0.45

The one caveat to all of this (When getting the nearest number is OK) is
that the data in your
leftmost column must be sorted in ascending order.


There are times when you would only want it to give you a value if you had
an exact match on the
number, and in this instance you would simply add a 4th argument of 0 or
FALSE to the formula,
eg:-

=VLOOKUP(A1,D1:E5,3,0)

or

=VLOOKUP(A1,D1:E5,3,FALSE)

In these cases you do not need to have the data in your leftmost column
sorted.


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"JohnT" wrote in message
...
I guess I'm a bit thick because I am just not getting it after looking at
help and searching the forum here...
I have a separate worksheet that has 2 columns, an item number and a
description. I would like to NOT have to enter the description each time
in
separate worksheets so I thought VLOOKUP would be the way to go.
I have accounts in different tabs (worksheets) in a workbook and have
created a tab (worksheet) with the item number and description only: Items
is
the name of the worksheet and columns A2 to A200 have the number and B2 to
B200 the corresponding description.
Something I'm doing is wrong because this isn't working:
VLOOKUP(Items!a2:a200,Description,False)
Could someone please help out?
Thanks in advance!!!

=VLOOKUP("lookup_value",Items!a2:B200,2,False)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnT" wrote in message
...
I guess I'm a bit thick because I am just not getting it after looking at
help and searching the forum here...
I have a separate worksheet that has 2 columns, an item number and a
description. I would like to NOT have to enter the description each time
in
separate worksheets so I thought VLOOKUP would be the way to go.
I have accounts in different tabs (worksheets) in a workbook and have
created a tab (worksheet) with the item number and description only: Items
is
the name of the worksheet and columns A2 to A200 have the number and B2 to
B200 the corresponding description.
Something I'm doing is wrong because this isn't working:
VLOOKUP(Items!a2:a200,Description,False)
Could someone please help out?
Thanks in advance!!!

Thanks very much. I found that I hadn't selected the correct range too and
your bit of help got me over the hump!
Thanks again!

"CoRrRan" wrote:

You are using the VLOOKUP-function incorrectly:

It should be something like this:
=Vlookup
(ToBeLookedUpValue,RangeContainingValue,ColumnOfRa ngeContainingToBeFoundI
tem,False)

Which would be something like:
Range A1 contains value 100
Range A2 to A200 in worksheet "Items" contain numbers from 1 to 199 by
example), including "100".
Range B2 to B200 in worksheet "Items" contain the descriptions for items
1 to 199

To get the description for value "100", do this:

=Vlookup(A1,Items!A2:A200,2,False)

HTH,
CoRrRan

"?B?Sm9oblQ=?=" wrote in
:

I guess I'm a bit thick because I am just not getting it after looking
at help and searching the forum here...
I have a separate worksheet that has 2 columns, an item number and a
description. I would like to NOT have to enter the description each
time in separate worksheets so I thought VLOOKUP would be the way to
go. I have accounts in different tabs (worksheets) in a workbook and
have created a tab (worksheet) with the item number and description
only: Items is the name of the worksheet and columns A2 to A200 have
the number and B2 to B200 the corresponding description.
Something I'm doing is wrong because this isn't working:
VLOOKUP(Items!a2:a200,Description,False)
Could someone please help out?
Thanks in advance!!!