|
Some rules about Vlookup REPOSTED
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 |
Some rules about Vlookup REPOSTED
On Jan 26, 1:55*pm, "L. Howard Kittle" wrote:
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: * 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" wrote in message ... 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- Hide quoted text - - 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 |
Some rules about Vlookup REPOSTED
|
Some rules about Vlookup REPOSTED
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 "JohnC" wrote in message ... 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 |
Some rules about Vlookup REPOSTED
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 On Jan 26, 4:50*am, JohnC wrote: 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 |
Some rules about Vlookup REPOSTED
On Jan 26, 6:03*pm, Pete_UK wrote:
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 On Jan 26, 4:50*am, JohnC wrote: 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- Hide quoted text - - 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 |
| All times are GMT +1. The time now is 04:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com