VLOOKUP problem

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
Thoroughbred
6/2/2010 10:11:34 PM
mac.office.excel 1146 articles. 0 followers. Follow

5 Replies
1091 Views

Similar Articles

[PageSpeed] 15

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
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
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
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
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
John_McGhie_
6/5/2010 5:48:37 AM
Reply:

Similar Artilces:

Query problem #2
Hello, I know how to set up query parameters where the paramater entered provides a value for one of the query fields. What I am struggling with is setting up a parameterised query where the parameter entered is the first character in a field. In other words I want to parameteries a "begins with" criteria. In the Value field within MS Query I have entered Like '[parameter]%' which obviously isn't working. Can anyone tell me the correct syntax please? Thanks in advance Will replace the % with *. Different systems use different wildcard characters to make life spicier...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Calendar and Quicken problem
I imported my Quicken data file with no problem (I think!?). However, the first time I opened the file, there were hundreds of scheduled transactions on the calendar in the future. So I deleted the bogus transactions, and Money now doesn't show ANY transactions in the middle tray, even though I see the money in the register. Any ideas on how to get the transacions back on the car. Regards, Mark ...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Focus problem when using UI thread
Hi all- I have an app that uses a separate ui thread for popping up a dialog box when the app is doing something that takes awhile (like a big query). The dialog box simply shows a "please wait" message, then when the query is done, the dialog box and its thread are destroyed (this is done through a separate class that spawns the thread which shows the dialog box). The problem is that it seems sometimes when the dialog box is removed, and I'm about to show *another* dialog box, that belongs to the main thread, the focus gets screwed up...some other window will be pushed to the ...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Outlk 02 group send problem
When I want to send an email to a group it acts as if it's sending but nobody receives the email. Whether I select one group or select multiple individuals the email does not send. It sounds like you have two problems. Sending to a group and sending to more than one person. How do you insert the names on the TO: line? Easiest way is to click on the TO: and then highlight each user / group and then dont forget to click on the TO: / CC / BCC button to move highlighted name to the reciepient box. Thom >-----Original Message----- >It sounds like you have two problems. Send...

Font problem in Publisher 2002
I am a printer that has clients submit artwork in Publisher 2002. I have problems with some fonts printing correctly. Sometimes they print as symbols and other times they print as a different font even though the correct font is installed. This problem occurs to a postscript printer and non-postscript printer. I can save the document back to Publisher 2000 and correct the font problem but in doing so I create other problems. The font even looks wrong on my screen. I have loaded and unloaded the program and the problem still exsists. NEED HELP!!! After managing to set up OE-Quo...

outlook start up problems
Since I've installed Windows XP SP-2 my Outlook program is starting each time up as I open my computer. Searching in the registry didn't solved my problem at all because theirs any sign that shows outlook in the start up menu. How can I solve this problem? Regards Johan <anonymous@discussions.microsoft.com> wrote: > Since I've installed Windows XP SP-2 my Outlook program > is starting each time up as I open my computer. Searching > in the registry didn't solved my problem at all because > theirs any sign that shows outlook in the start up menu. Does it...

Problem with report
I tried to ask this on the reports area but got zero response. Is this to easy or is there no answer? I have tried everything to eliminate the problem but nothing seems to work. It now does not work in Access 2003 either. Interesting, it has work at client site for three years or more until they installed Access 2007. ANY ideas??? I have a report that I created with Access 2003 that is 8 1/2 x 3 1/2 inches. It worked great in Access 2003, but in Access 2007 it prints the 1st page ok (info centered where I want it), but prints the info on the 2nd page moved to the top of the page (almost l...

Footer Problems
ok, I did a search and couldn't find anything helpful. I created a footer with the document name on the left and the page number on the right. I added a top border too. Now, how can I hide the footer on the page 1? You would think it'd be pretty simple. Any help will be appreciated. Oh, is there a code I can add that would automatically insert the name of the document? Check the box for "Different first page" on the Layout tab of Page Setup. This will give you a separate First Page Footer that you can leave blank. -- Suzanne S. Barnhill Microsoft MVP ...

Windows Live Password Problems with Archive Files
I am able to login to my current Microsoft Money file using my current Windows Live ID and password. However, I need to look up something in a Money archive file. When I try to open the archive file and sign in, I get an error that my email or password is incorrect. Resetting my Windows Live password did not help - I continue to get the error message. Any ideas for what's going on here? I use Microsoft Money Plus Deluxe, Windows XP. Very tough to say. Do you know what version of Money you were using when you created the archive file? For many reasons--some JUST LIKE THIS--I r...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

Requery combo box problem (error 2118)
This is a question about an Access 2000 database. I'm going to ask the question in the way that I'm trying to get it to work, but if there seems like there is a better design to avoid the problem altogether that I haven't thought of, please mention it. I have a form (lets say Form1) with a field that uses a combo box that draws its data from table tblMachines. If the user searches through the combo box for a particular choice and doesn't find it, the user would then open up another form (Form2), which directly shows data from table tblMachines. There, the user can add a new...

Combo box problem to Report
I have a table with 160 records. I am trying to set up my form so the user can select a record from a combo box, enter the payroll in a text box field and submit. Then they can perform the same function several times and each record will show up on my report with the payroll for each separate record. I’ve tried the combo box on the form page but when you select one or several selections, all 160 records are displayed on the reports page. Would also like a suggestion on how to have it set up for the user to enter the payroll (automatically rounded to the closest dollar) each time they ...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...

Problem after MS Update KB978207
Hi All, After installing KB978207 we found we had no network connection and IPSec eventId: 7023. Has anyone else installed this yet or found a solution to this? Thanks, Nick My guess is this is that old DNS reserved ports issue that can happen randomly on any reboot and has nothing to do with KB978207. Make sure you add the DNS reservation patch to fix your IPSec booting issue: http://msmvps.com/blogs/bradley/archive/2009/03/11/sbs-2003-and-dns-patch-issues-revisited.aspx -- Allan Williams Nick wrote: > Hi All, > > After installing KB97820...

SERIOUS: CRM problem when attaching file
Hi We have implemented SPLA CRM, with clients using the Outloop laptop client. We have a serious issue when you attempt to attach a file to a CRM entiy by clicking the paper clip icon. When you click this icon, you get the standard "Add Attachment" popup. You browse to a file, then click attach, and instead of attaching the file (under the Notes section), you get this bizare looking form titled "CRM Sample Logon Form". This form has Username and Password fields, and a LogOn button. This happens for any entity. This error does not occurr when usign the web client. ...

tab control problem
i have a tab control. there are dialogs in each tab. when i press enter in a tab , the dialog in the tab disappears. however, when i go another tab and choose the disappeared tab it is shown again. i guess when the focus is on the dialog in that tab , and the enter is pressed it disappears. how can i prevent disappear of dialogs in the tabs when i press enter? murat murat aydin wrote: > i have a tab control. there are dialogs in each tab. when i press enter in a > tab , the dialog in the tab disappears. however, when i go another tab and > choose the disappeared tab it is shown ...

problem with toolbar?
Hi, I have a very large spreadsheet which is used by many people. I have been updating this spreadsheet so that it is more effecient, so far successfully. I have just created a new toolbar which has on it, buttons which I created which link to macro's. The problem I have is that everything looks great on my pc, however when another person looks at the spreadsheet, it is updated with everything except the new toolbar. I have saved it correctly and the other people are opening the correct file. Anyone have any thoughts which would help????????? --- Message posted from http://www.ExcelForum...

Chart problem.
I have a chart that plots body mass index, ie value on y axis, and dates on x axis. I have also added a second category x as I have divided the chart into horizonatl zones with different colors (ie underweight, overweight etc). Things work fine except that when I plot, for instance, three dates (Jan 1, Feb 1, March 1). The Jan point is right on the left side Y axis, the Feb is mid way across the chart, and the March is on the other end of the chart (right Y axis) I'd like to narrow down the spaces between each data point, and also have some space at the beginning of the chart ...

VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...