Vlookup

[Guest]

In another worksheet, I have a table sorted in ascending order. Table has
four columns. The first column has my lookup information in it, and the
other three, is the informatin that I am trying to get into another workbook.
But all I get is #N/A.

Example: In [spreadsheet A] there is a table that has in col 1 a list of
names, col 2 through 4 has a set of numbers. Now in [spreadsheet B], I am
trying to use the vlookup function to tell it what to look for in spreadsheet
A. My problem is how do I tell it what col to go to in [spreadsheet A] to
get the number needed, if [spreadsheet B] has a match with [spreadsheet A]
table.

 

[Rick Rothstein \(MVP - VB\)]

In another worksheet, I have a table sorted in ascending order. Table has

four columns. The first column has my lookup information in it, and the
other three, is the informatin that I am trying to get into another
workbook.
But all I get is #N/A.

Example: In [spreadsheet A] there is a table that has in col 1 a list of
names, col 2 through 4 has a set of numbers. Now in [spreadsheet B], I am
trying to use the vlookup function to tell it what to look for in
spreadsheet
A. My problem is how do I tell it what col to go to in [spreadsheet A] to
get the number needed, if [spreadsheet B] has a match with [spreadsheet A]
table.

=VLOOKUP(NameToFind,RangeContainingNamesNumbers,NumberColumn)

As an example, place this in SpreadSheetB...

=VLOOKUP("Rick",SpreadSheetA!A1100,3)

It will look up the name "Rick" in column A of the range A1100 on
SpreadSheetA and return the value in column C for the row that "Rick" was
found on.

Rick

 

[Guest]

=VLOOKUP(A2,Sheeta!A,2,0)

will return data from column B (defefined by the 2 in the VLOOKUP) if A2 in
Sheet B matches with column A in Sheet A

=VLOOKUP(A2,Sheeta!A,3,0)

will return value in column C

=VLOOKUP(A2,Sheeta!A,4,0)

will return value in column D


For future queries: please supply any formula/data information to assist in
getting a more complete reply e.g. your VLOOKUP formula in tis case.

HTH

 

[Guest]

You need to include the other workbook's name and sheet name as part of the
lookup range in the VLOOKUP statement. This is easiest to do with both
workbooks open. A formula that might look something like this when just
dealing with 2 sheets in a single workbook:
=VLOOKUP("Ralph",Sheet1!$A$1:$B$4,2,0)
would look something like this when looking into another workbook:
=VLOOKUP("Ralph",[Book1.xls]Sheet1!$A$1:$B$4,2,0)

Just like a regular VLOOKUP, the 3rd parameter (the ,2, in both of the
above) tells it to return the value from the 2nd column of that table in this
example.

Easy way to get Excel to do the work for you is to have both workbooks open.
Go to where you want the VLOOKUP to be and start it out by typing:
=VLOOKUP(
and then click the cell with the value to be looked up, which will put that
address into the formula, then type a comma so that things look like this at
this point:
=VLOOKUP(A9,
then go to the other workbook, to the needed sheet in it and select the
range of cells that make up the table. The whole workbook/sheet/cells
portion will be filled in for you, again type a comma, which might give you
something looking like this at that point
=VLOOKUP(A9,[Book1.xls]Sheet1!$A$1:$B$4,
now just finish it out by typing in the number for the column you want to
return information from. Remember that this number is the relative column
number within the lookup table and really doesn't have anything to do with
the column numbers of all columns on the sheet. Then finish it out and press
[Enter] so you have a formula that looks something like one of these:
=VLOOKUP(A9,[Book1.xls]Sheet1!$A$1:$B$4,2,0)
=VLOOKUP(A9,[Book1.xls]Sheet1!$A$1:$B$4,2,1)

More about the column number: if you had a table that went from A1 to D50,
then column 2 in it is column B. But if your table goes from Z1 to AC50,
then column number 2 is column AA on the sheet.

Does that help any?

 

[Guest]

Thanks folks for the information I thought I knew how to do a vlookup, but
this one has me stumped.

My formula is the following: =VLOOKUP("Rick",'Sheet A'!L6:O47,4), which if
I am not mistaken is just like what you folks are telling what to do. But
every time this formula is returning a #N/A. The columns that have the
lookup and the corresponding column have been checked and both are text
columns. I also just before writing this checked that my number columns that
they were in the number format, and they are. So I am at a lost as to what
is causing the #N/A to appear. A thought has just struck me, if I have some
of the names but not all of my names hyphened, would that cause the #N/A to
appear?

 

[Rick Rothstein \(MVP - VB\)]

Thanks folks for the information I thought I knew how to do a vlookup, but

this one has me stumped.

My formula is the following: =VLOOKUP("Rick",'Sheet A'!L6:O47,4), which
if
I am not mistaken is just like what you folks are telling what to do. But
every time this formula is returning a #N/A. The columns that have the
lookup and the corresponding column have been checked and both are text
columns. I also just before writing this checked that my number columns
that
they were in the number format, and they are. So I am at a lost as to
what
is causing the #N/A to appear. A thought has just struck me, if I have
some
of the names but not all of my names hyphened, would that cause the #N/A
to
appear?

I'm not sure about the #N/A (the hyphen should not matter), but the names in
column L must be sorted in ascending order. If that is not the case with
your spreadsheet, then select the range L6:O47 on 'Sheet A', click Date/Sort
from Excel's menu and Sort By column L only (Ascending) and see if that
straightens things out.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Thanks folks for the information I thought I knew how to do a vlookup, but

I'm not sure about the #N/A (the hyphen should not matter), but the names
in column L must be sorted in ascending order. If that is not the case
with your spreadsheet, then select the range L6:O47 on 'Sheet A', click
Date/Sort from Excel's menu and Sort By column L only (Ascending) and see
if that straightens things out.

***OR*** don't sort your data and use your original formula, but add the
optional 4th argument of FALSE so that the table doesn't have to be sorted.

Rick

 

[Guest]

Rick and all,

I think I just identified my problem, but have not figured out a way around
it.

In my text column the hyphen does come into play so to speak, here is why.
I have a person who is in charge of a group of people, so what I have done is
use the name of the leader, then a hyphen and then his worker. Here is a
example or two: [David-Paul, David-Steve, David-Saul, David-Tim]. Now the
workers can go between the leaders, so it could also be [Frank-Paul,
Frank-Steve, Frank-Saul, Frank-Tim]. BTW the text column is sorted in
ascending order.

So what I believe the problem is that the vlookup function can not match the
names because it sees the same name mutilate times as the first name.

Is this correct, and if so how would I fix the problem?

 

[Sandy Mann]

I would think that the reason that reason that you are having trouble is
that the lookup value is not being found because it has an invisible
charcter at one end of the name.

Try using the formula =Len(Cell reference that should match your VLOOKUP)
and see if it returns a different number than the number of charcters that
seem to be in the cell.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Both have the lenght of 14 characters

I would think that the reason that reason that you are having trouble is
that the lookup value is not being found because it has an invisible
charcter at one end of the name.

Try using the formula =Len(Cell reference that should match your VLOOKUP)
and see if it returns a different number than the number of charcters that
seem to be in the cell.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

That is very likely your problem - 'David-Paul' is not the same as
'David-Steve' if you're looking for David, and 'Frank-Paul' and 'David-Paul'
are not the same if you're looking for 'Paul'. In point of fact, there would
be no match (#N/A) for a search of the hyphenated entries with any single
name as the search-for parameter.

Also, VLOOKUP() stops looking when it does find the first match, so if you
had 15 "David" entries in your lookup table and wrote a VLOOKUP() formula to
find "DAVID" in that table, it would always, and only, return information
about the first of the 15 entries.

Depending on how your table is set up, this may provide a workaround.

I'm assuming the hypnenated entries are the 1st column of your lookup table
and you're seeking information about particular pairings? If so, then what
you could do for the VLOOKUP() side of things is have 2 cells (F1 and G1 for
example's sake) which you'd put the supervisor's name into F1 and the
employee's name into G1. Then, your formula would look like this:
=VLOOKUP(F1 & "-" & G1,'Sheet A'!L6:O47,4,0)
if you have spaces on either side of the hyphen, include them within the "-"
as " - " so that the created text string will match what you have in the
table entries.

If you had Frank in F1 and Saul in G1, then at that time the formula would
equate to having typed it as: =VLOOKUP("Frank-Saul",,'Sheet A'!L6:O47,4,0)

By changing the entries in F1 and G1, you change the VLOOKUP itself. Hope
this helps some.