Some rules about Vlookup REPOSTED

[JohnC]

Most posts are about something that doesnt work ,This is about
something that works but shouldn't work
I have read in many places the vlookup only works with first column
data -but I have tried it with other columns and it seems to work -

What are the real rules for Vlookup ?

This is the Look up array

001 John
002 Fred
003 Albert

Tables to lookup
Example 1

Person Year City Vlookup Result
001 2009 London John
002 2000 Paris Fred
003 2001 Tokyo Albert
001 2003 Madrid John

= VLOOKUP(A2,Array,2,FALSE) works

Example 2

Year Person City Vlookup Result
2009 001 London John
2000 002 Paris Fred
2001 003 Tokyo Albert
2003 001 Madrid John

= VLOOKUP(B2,Array,2,FALSE) works


Example 3

Year City Person Vlookup Result
2009 London 001 John
2000 Paris 002 Fred
2001 Tokyo 003 Albert
2003 Madrid 001 John

= VLOOKUP(C2,Array,2,FALSE) works

Unless I am totally confused I thought it will only work for example
no 1
ie data in A column

 

[L. Howard Kittle]

Hi John,

Pete and Gord gave you some solid advice on how a VLOOKUP works in your
previous post. So I think you are just not digesting the instructions.

Send me an example workbook with the puzzling vlookup's you have and I will
give it a shot to: (e-mail address removed)

Show what you think is working and what is not as you see it. Along with
detailed instructions of what you expect to happen and where it should
happen.

And perhaps try this simple example:

In D1 enter =VLOOKUP(C1,A1:B3,2,0)

A1:A2 enter 1 2 3
B1:B3 enter a b c
C1 enter either 1 2 or 3

In D1, 1 returns the a, 2 returns the b and 3 returns the c.

In the formula:

First Argument: C1 is the Lookup_value
Second Argument: A1:B3 is the Table_Array
Third Argument: 2 is the Col_Index_Num
Fourth Argument: 0 or False looks for an exact match (1 or True will return
the closet match smaller than an exact match if there is no exact
match)

HTH
Regards,
Howard

 

[JohnC]

Hi John,

Pete and Gord gave you some solid advice on how a VLOOKUP works in your
previous post.  So I think you are just not digesting the instructions.

Send me an example workbook with the puzzling vlookup's you have and I will
give it a shot to:   (e-mail address removed)

Show what you think is working and what is not as you see it.  Along with
detailed instructions of what you expect to happen and where it should
happen.

And perhaps try this simple example:

In D1 enter =VLOOKUP(C1,A1:B3,2,0)

A1:A2 enter 1 2 3
B1:B3 enter a b c
C1 enter either 1 2 or 3

In D1, 1 returns the a, 2 returns the b and 3 returns the c.

In the formula:

First Argument:  C1 is the Lookup_value
Second Argument:  A1:B3 is the Table_Array
Third Argument:  2 is the Col_Index_Num
Fourth Argument:  0 or False looks for an exact match (1 or True will return
the        closet match smaller than an exact match if there is no exact
match)

HTH
Regards,
Howard



















- Show quoted text -

Thanks for responding Howard-

Actually Gord asked for an example and that what I gave above-

My problem is not that is doesn't work -my problem is that it does -
contrary to everything I read

I have seen references to only link data that is is column A and if
its not in the A column create a temp column and insert it in there
Yet as you can see from my example above it seems to work in any
column A, B,C

 

[L. Howard Kittle]

Hi John,

I would still like to see your worksheet of what is working when it should
not. Send an attachment to an e-mail to (e-mail address removed) with some
explanations of what is happening an where.

In your example...

This is the Look up array

001 John
002 Fred
003 Albert>

There is no such thing in vlookup as "Look up array"
There is no such thing as "Tables to lookup".
This could be the "Table_Array as you display here:

Person Year City Vlookup Result
001 2009 London John
002 2000 Paris Fred
003 2001 Tokyo Albert
001 2003 Madrid John

But if this was the Table_Array it would not work because of the duplicate
001, 001 in the first column. The vlookup would return the first 001 of
either 2009, London, or John depending on what the Col_Index_Num was and
ignore the second 001.

I do not understand what is working in in what I believe is an unworkable
vlookup situation...?

Regards,
Howard

 

[L. Howard Kittle]

The Play on the Field is Being Reviewed...
After futher review...

Are you confusing the "First column" as ONLY column A on a worksheet?

If so, that is wrong. In VLOOKUP "First column" refers to the most left
column of the Table_Array.

If the Table_Array is F1:K500 then the Lookup_Value will be searched in
column F. When found it will return the value that is designated by
Col_Index_Num which can be column G, H, I, J, or K. (2, 3, 4, 5, 6 for
example as the third argument in the formula)

And to just shake your tent even more, it is possible return ALL the values
of G, H, I, J, and K from the value found in F with VLOOKUP.

But lets solve this first.

Howard

 

[Pete_UK]

It is the first column of the lookup array which is used for finding a
match - your lookup data can be in any column (in your cases A, B or
C).

Hope this helps (again).

Pete

 

[JohnC]

It is the first column of the lookup array which is used for finding a
match - your lookup data can be in any column (in your cases A, B or
C).

Hope this helps (again).

Pete
















- Show quoted text -

Pete / Howard
Thank you yes you are right somewhere I saw that both the lookup
column and the array refrence had to be the first column which is wrong