Vlookup problem is killin me.

S

seeinred04

i have a data base on sheet 1, formula is on sheet 2. its sorted ascending
everything looks right, but it returns the value from the correct column but
always picks from the previous row.
 
R

RagDyer

Are you using all 4 arguments of the function?

Is the 4th argument 0 or "False", denoting that an exact match is called
for?

If "yes" to the above, post the formula you're using.
 
D

daddylonglegs

If you're looking for an exact match

=VLOOKUP(G2,Sheet1!A1:F19677,5,0)

no sorting required
 
R

RagDyer

It sounds like you have a data problem.
Are your values imported, returns from other formulas, or keyed in?

BTW ... What happens with this formula:

=Index(Sheet1!E1:E19677,Match(G2,Sheet1!A1:A19677,0))
?
 
S

seeinred04

values are imported from another software type. numbers are stored as text,
i have combined 2 columns (first & last names) to read as one variable. Is
there a way that i can "lookup" an ID # based on multiple cells to avoid any
problems from combining them?

BTW- same resukt from index formula?

Thanks a bunch for tryin to help a greenhorn.
 
R

RagDyeR

We're now 7 posts deep into this thread, and just *now* we learn of text
numbers and combined (exactly what that means ... I don't know) columns.

SO ... to help, we need an understandable description of your datalist, the
one that the formula will perform computations on.

What type of data is in each *pertinent* (what the formula will reference)
column, and what type of data is in the column used as the lookup value that
the formula will poll.

Being thorough and still concise might be considered an art.

This link I garnered from Jim Cone is *very* apropos:



The people around here enjoy solving problems and helping.

We're all very selfish!

We race and trip over each other in order to get you the help you want.

Give us the ammo (information) so that we can bask in our own enjoyment of
helping you.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


values are imported from another software type. numbers are stored as text,
i have combined 2 columns (first & last names) to read as one variable. Is
there a way that i can "lookup" an ID # based on multiple cells to avoid any
problems from combining them?

BTW- same resukt from index formula?

Thanks a bunch for tryin to help a greenhorn.
 
R

RagDyeR

Whenever the word "imported" is mentioned, there is always the possibility
of additional, invisible characters being included within that imported
data.

I would start off by checking the length of the I and J columns using,
=Len(I2)
=Len(J2)

And comparing the results to a visual count.
Do they all match?

BTW ... when you receive a return of the name from the row above the correct
name, does the formula you used call for approximate matches (ends in 1 or
TRUE)?

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

First off, I'm sorry for the confusion. I am under a time crunch, and
figured that it was probably a stupid mistake on my part that would be
easily
resolved. It's still gonna be a stupid mistake on my part but. I am new to
posting questions and I am not for sure what may or may not be pertinent
information. That being said, here we go.

My data is exported from a Sage Software. Basically it is detailed
directory of people that meet certain criteria. Some meet more than one so
they are listed more than once. The data contains an ID number, last name,
first name, Address, etc. I have another list that was exported from
InfoPoint. It has basically the same info EXCEPT, it has an Account Number
instead of ID and only includes unique records. I need both the Account and
ID numbers in one spreadsheet. The only thing that I knew to reference is
full name, which is in 2 separate columns. So I combined the 2 columns by
way of: =I2&" "&J2 for both lists. Then on one of the lists I am
adding a column with the lookup formula to lookup the ID number based on
name. I have tried:
=VLOOKUP(H2,Sheet1!A1:G19677,5)
=VLOOKUP(H2,Sheet1!A1:G19677,5,0)
=VLOOKUP(H2,Sheet1!A1:G19677,5,1)
=VLOOKUP(H2,Sheet1!A1:G19677,5,FALSE)
=VLOOKUP(H2,Sheet1!A1:G19677,5,TRUE)
=INDEX(Sheet1!F2:F19677,MATCH(H3,Sheet1!B2:B19677,1))
All of which result in either #N/A or the ID number of the person in the row
just above who I need.

I hope this is clear and not too long but I'm simple minded and had to
explain it to myself several times during this explanation. Thanks.
 
Top