VLOOKUP problem

  • Follow


Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

VLOOKUP Looks in the FIRST COLUMN of an array and moves across the row to return the value of a cell. <br><br>How would you do this same thing, except look up a value in a column BESIDES the first one, and return a value on the same row in a completely different column?? Does excel even offer this function? <br><br>This can be more useful... Let's say, for example, you have information saved in columns. The first column stores the names of some items, the second stores cost. You want to return the name of the item with the least cost. VLOOKUP will only look up values in the first column, so there is no way for you to search for the cost in the second column. (The goal was to look for the least cost in the second column, then return the name of the item). The only way I see to fix this is to store costs in the first column and name in the second, but that shouldn't be necessary. And plus if you then need to look up an item's cost starting with the name, you won't be able to now that the names aren't in the first column. <br><br>So I'm wondering if there is another way to do this that I am not thinking of or if excel simply doesn't offer this function.
0
Reply Thoroughbred 6/2/2010 10:11:34 PM

You define a named range such that the value you are searching is the first
column.

It makes no difference which column it is physically, if you have a range
Named "MyLookup", then your Cost column can be the first column of that
range.

Hope this helps


On 3/06/10 8:11 AM, in article 59bb94bc.-1@webcrossing.JaKIaxP2ac0,
"Thoroughbred@officeformac.com" <Thoroughbred@officeformac.com> wrote:

> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
> VLOOKUP Looks in the FIRST COLUMN of an array and moves across the row to
> return the value of a cell.
> 
> How would you do this same thing, except look up a value in a column BESIDES
> the first one, and return a value on the same row in a completely different
> column?? Does excel even offer this function?
> 
> This can be more useful... Let's say, for example, you have information saved
> in columns. The first column stores the names of some items, the second stores
> cost. You want to return the name of the item with the least cost. VLOOKUP
> will only look up values in the first column, so there is no way for you to
> search for the cost in the second column. (The goal was to look for the least
> cost in the second column, then return the name of the item). The only way I
> see to fix this is to store costs in the first column and name in the second,
> but that shouldn't be necessary. And plus if you then need to look up an
> item's cost starting with the name, you won't be able to now that the names
> aren't in the first column.
> 
> So I'm wondering if there is another way to do this that I am not thinking of
> or if excel simply doesn't offer this function.

 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name


0
Reply John_McGhie_ 6/3/2010 10:01:36 AM


Can you give me an example? Because I've tried this and it didn't work.
0
Reply Thoroughbred 6/3/2010 8:23:33 PM

> You define a named range such that the value you are searching is the first <br>
> column. <br>
>  <br>
> It makes no difference which column it is physically, if you have a range <br>
> Named &quot;MyLookup&quot;, then your Cost column can be the first column of that <br>
> range. <br>
>  <br>
> Hope this helps <br>
>  <br>
>  <br>
> On 3/06/10 8:11 AM, in article 59bb94bc.-1@webcrossing.JaKIaxP2ac0, <br>
> &quot;Thoroughbred@officeformac.com&quot;  wrote: <br>
>  <br>
> > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel <br>
> > VLOOKUP Looks in the FIRST COLUMN of an array and moves across the row to <br>
> > return the value of a cell. <br>
> >  <br>
> > How would you do this same thing, except look up a value in a column BESIDES <br>
> > the first one, and return a value on the same row in a completely different <br>
> > column?? Does excel even offer this function? <br>
> >  <br>
> > This can be more useful... Let's say, for example, you have information saved <br>
> > in columns. The first column stores the names of some items, the second stores <br>
> > cost. You want to return the name of the item with the least cost. VLOOKUP <br>
> > will only look up values in the first column, so there is no way for you to <br>
> > search for the cost in the second column. (The goal was to look for the least <br>
> > cost in the second column, then return the name of the item). The only way I <br>
> > see to fix this is to store costs in the first column and name in the second, <br>
> > but that shouldn't be necessary. And plus if you then need to look up an <br>
> > item's cost starting with the name, you won't be able to now that the names <br>
> > aren't in the first column. <br>
> >  <br>
> > So I'm wondering if there is another way to do this that I am not thinking of <br>
> > or if excel simply doesn't offer this function. <br>
>  <br>
>  -- <br>
>  <br>
> The email below is my business email -- Please do not email me about forum <br>
> matters unless I ask you to; or unless you intend to pay! <br>
>  <br>
> John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer, <br>
> McGhie Information Engineering Pty Ltd <br>
> Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name <br>
>  <br>
>  <br>
>  <br><br>Actually I got it to work, but what if the column of the value it needs to return is in a column preceding the one that you are looking in?
0
Reply Thoroughbred 6/3/2010 8:29:06 PM

Nope, the lookup values *must* be in the first (leftmost) column of the
lookup table, so the lookup has to scan L-R. IOW, as far as the function is
concerned there are no columns preceding (to the left of) the one containing
the values being scanned :-)

Regards |:>)
Bob Jones 
[MVP] Office:Mac



On 6/3/10 4:29 PM, in article 59bb94bc.2@webcrossing.JaKIaxP2ac0,
"Thoroughbred@officeformac.com" <Thoroughbred@officeformac.com> wrote:

>> You define a named range such that the value you are searching is the first
>> column. 
>>  
>> It makes no difference which column it is physically, if you have a range
>> Named "MyLookup", then your Cost column can be the first column of that
>> range. 
>>  
>> Hope this helps 
>>  
>>  
>> On 3/06/10 8:11 AM, in article 59bb94bc.-1@webcrossing.JaKIaxP2ac0,
>> "Thoroughbred@officeformac.com"  wrote:
>>  
>>> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
>>> Intel 
>>> VLOOKUP Looks in the FIRST COLUMN of an array and moves across the row to
>>> return the value of a cell.
>>>  
>>> How would you do this same thing, except look up a value in a column BESIDES
>>> the first one, and return a value on the same row in a completely different
>>> column?? Does excel even offer this function?
>>>  
>>> This can be more useful... Let's say, for example, you have information
>>> saved 
>>> in columns. The first column stores the names of some items, the second
>>> stores 
>>> cost. You want to return the name of the item with the least cost. VLOOKUP
>>> will only look up values in the first column, so there is no way for you to
>>> search for the cost in the second column. (The goal was to look for the
>>> least 
>>> cost in the second column, then return the name of the item). The only way I
>>> see to fix this is to store costs in the first column and name in the
>>> second, 
>>> but that shouldn't be necessary. And plus if you then need to look up an
>>> item's cost starting with the name, you won't be able to now that the names
>>> aren't in the first column.
>>>  
>>> So I'm wondering if there is another way to do this that I am not thinking
>>> of 
>>> or if excel simply doesn't offer this function.
>>  
>>  -- 
>>  
>> The email below is my business email -- Please do not email me about forum
>> matters unless I ask you to; or unless you intend to pay!
>>  
>> John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
>> McGhie Information Engineering Pty Ltd
>> Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name
>>  
>>  
>>  
> 
> Actually I got it to work, but what if the column of the value it needs to
> return is in a column preceding the one that you are looking in?

0
Reply CyberTaz 6/4/2010 10:49:06 PM

Then use Index and Match.  See:

http://www.ozgrid.com/Excel/left-lookup.htm

Cheers




On 4/06/10 6:29 AM, in article 59bb94bc.2@webcrossing.JaKIaxP2ac0,
"Thoroughbred@officeformac.com" <Thoroughbred@officeformac.com> wrote:

>> You define a named range such that the value you are searching is the first
>> column. 
>>  
>> It makes no difference which column it is physically, if you have a range
>> Named "MyLookup", then your Cost column can be the first column of that
>> range. 
>>  
>> Hope this helps 
>>  
>>  
>> On 3/06/10 8:11 AM, in article 59bb94bc.-1@webcrossing.JaKIaxP2ac0,
>> "Thoroughbred@officeformac.com"  wrote:
>>  
>>> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
>>> Intel 
>>> VLOOKUP Looks in the FIRST COLUMN of an array and moves across the row to
>>> return the value of a cell.
>>>  
>>> How would you do this same thing, except look up a value in a column BESIDES
>>> the first one, and return a value on the same row in a completely different
>>> column?? Does excel even offer this function?
>>>  
>>> This can be more useful... Let's say, for example, you have information
>>> saved 
>>> in columns. The first column stores the names of some items, the second
>>> stores 
>>> cost. You want to return the name of the item with the least cost. VLOOKUP
>>> will only look up values in the first column, so there is no way for you to
>>> search for the cost in the second column. (The goal was to look for the
>>> least 
>>> cost in the second column, then return the name of the item). The only way I
>>> see to fix this is to store costs in the first column and name in the
>>> second, 
>>> but that shouldn't be necessary. And plus if you then need to look up an
>>> item's cost starting with the name, you won't be able to now that the names
>>> aren't in the first column.
>>>  
>>> So I'm wondering if there is another way to do this that I am not thinking
>>> of 
>>> or if excel simply doesn't offer this function.
>>  
>>  -- 
>>  
>> The email below is my business email -- Please do not email me about forum
>> matters unless I ask you to; or unless you intend to pay!
>>  
>> John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
>> McGhie Information Engineering Pty Ltd
>> Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name
>>  
>>  
>>  
> 
> Actually I got it to work, but what if the column of the value it needs to
> return is in a column preceding the one that you are looking in?

 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name


0
Reply John_McGhie_ 6/5/2010 5:48:37 AM

5 Replies
755 Views

(page loaded in 4.091 seconds)


Reply: