VLOOKUP problem

[Thoroughbred]

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.

 

[John_McGhie_[MVP]]

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

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:[email protected]

 

[Thoroughbred]

Can you give me an example? Because I've tried this and it didn't work.

 

[Thoroughbred]

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 (e-mail address removed)2ac0,
> "[email protected]" wrote:
>
>
> --
>
> 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:[email protected]
>
>
>

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?

 

[CyberTaz]

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

 

[John_McGhie_[MVP]]

Then use Index and Match. See:

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

Cheers

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:[email protected]