Lookups #2

Hi,

I'm not having much luck with doing a simple lookup.  For simplicity
sake, I have a table with three columns: name (A), duration (B), and
priority (C).  The table is sorted (and constantly resorted) by
priority.  I need to be able to look up duration values (in B) by
their names (in A).  My understanding is that LOOKUP requires the
lookup array to be sorted by the lookup column.  In my case, I don't
and can't have it sorted that way, so the function returns invalid
values.  Is there a way to do this?


Thanks
Dmitry
0
11/27/2007 7:14:45 PM
excel 39879 articles. 2 followers. Follow

2 Replies
782 Views

Similar Articles

[PageSpeed] 6

=VLOOKUP(LookupValue,A:B,2,0)

no need to sort anything


-- 


Regards,


Peo Sjoblom


"dmitry" <dmitry.maven@gmail.com> wrote in message 
news:61e8d266-7af4-49c9-a50b-1b2b4dc4f460@a35g2000prf.googlegroups.com...
> Hi,
>
> I'm not having much luck with doing a simple lookup.  For simplicity
> sake, I have a table with three columns: name (A), duration (B), and
> priority (C).  The table is sorted (and constantly resorted) by
> priority.  I need to be able to look up duration values (in B) by
> their names (in A).  My understanding is that LOOKUP requires the
> lookup array to be sorted by the lookup column.  In my case, I don't
> and can't have it sorted that way, so the function returns invalid
> values.  Is there a way to do this?
>
>
> Thanks
> Dmitry 


0
terre081 (3244)
11/27/2007 7:37:02 PM
Use the formula  =VLOOKUP(A2,$A$2:$C$20,2,FALSE)

Select columns A:C and sort by priority.


Gord Dibben  MS Excel MVP

On Tue, 27 Nov 2007 11:14:45 -0800 (PST), dmitry <dmitry.maven@gmail.com> wrote:

>Hi,
>
>I'm not having much luck with doing a simple lookup.  For simplicity
>sake, I have a table with three columns: name (A), duration (B), and
>priority (C).  The table is sorted (and constantly resorted) by
>priority.  I need to be able to look up duration values (in B) by
>their names (in A).  My understanding is that LOOKUP requires the
>lookup array to be sorted by the lookup column.  In my case, I don't
>and can't have it sorted that way, so the function returns invalid
>values.  Is there a way to do this?
>
>
>Thanks
>Dmitry

0
Gord
11/27/2007 7:38:22 PM
Reply:

Similar Artilces:

autofill in Outlook #2
Hi! When we delete an old profile and have the users logon to create a new one, their autofill feature is no longer there. In other words, they complain that they use to be able to start to type the email address and it would automatically pop in options. now it doesn't. Is this a setting somewhere, Outlook or elsewhere, that we need to turn on? How can we save the previous list of rememberances? Any help would be greatly appreciated. Marcia This is a profile specific file that stores the past used addresses. So when you create a new profile, you'll lose this. As the u...

2 problems in excel
MS Office 97 upgraded to 2000 with premium J4q4p-7d3g....... available on request About 3-4 times a Year (I use Excel very often) I close today a file after normal saving and switch off the PC. Tomorrow when I switch on and I want to load the excel file from yesterday, the PC responds "the file is reserved for Ferdi (its me!) but you may open the file as read only file". Nobody else works with this PC. I open the read-only file and save it under another name. No damage. But why does this happen? I never use file protection. Only sometimes to protect cells against accidential o...

rekenen #2
hoe rekenen je met excel ...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Zip Codes #2
Help...When I format a zip code file it appears in the field to show the "0" at the beginning of the field but it does not show when I print. The only work around is change it to text. I thought a zip code would recognize it as a zero. any idears.... -- deanf@johnsbyrne.com Hi Dean excel doesn't know what you've entered into a cell, it only recognises the "type" of data (text, number, date etc) .. so it won't know that you've entered zip codes and therefore won't "treat" them in a special way. However, i'm interested in HOW you for...

printing emails #2
is there a way to get outlook express, outlook 2000, or outlook 2003 to stop printing the name at the top left of an email when you print it? ...

Renaming columns #2
I'd like to rename the A B C D at the top to the name of my columns, o at least leave the column headings at the top of my screen. Does anyone know how -- Message posted from http://www.ExcelForum.com Tools>Options>View, uncheck "Row and column headers". You can insert a row and a column with your own names, but they will not be recognized in formulas the same way as "A1". But you can define row and column names and use the intersection as an address in a formula. The intersection operator is a space. So if you defined the name "Material" for column ...

filtering data into different workheet #2
Thanks for the help but i managed to find my way using pivot tables. Thanks again Swmasso -- swmasso ----------------------------------------------------------------------- swmasson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=838 View this thread: http://www.excelforum.com/showthread.php?threadid=27045 ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

install crm 1.2 db
i had proplem installing cm in win 2k server new instaltion when i am trying to ad new database file in the instaltion give me an error he the password in sql is not set right i did not setup any password yet and how can i do so i just instal from the cd only please help ...

ADC problem #2
Hi to all. I have this scenario: Exchange Adm. Group 1 with one Exchange 5.5 and one Exchange 2003 Exchange Adm. Group 2 one exchange 2003 Exchange Adm. Group 3 one exchange 2003 I had installed ADC in the exchange 2003 in Exchange Adm. Group 2. The service crashed, and i couldn’t start it any more. I reinstalled de service, but now I don’t have any connections agreements so I can’t replicate with the exchange 5.5. When I try to run the connections agreements wizard he doesn’t detect any exchange 5.5 in the Adm. Groups 2 and 3 and I can’t finish. I can’t create manually the Configurati...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Cumulative Total #2
I am trying to create a running total of sales objective achievement. For example, If in August the objective is 20 and I actually sold 25, then I am at positive 5. Then in Sept, my objective is 30 and I sold 20, I am now at negative 5. I do not want the number to change in my monthly column, but I would like for it to update in my fiscal year column based upon sales for each month. Is it possible to do this and if so how? I don't exactly understand if you want the running total to be alongside the monthly totals in a separate column. Labels in row1 Columns A = Date (Month) B =...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

--------Profit________________ #2
When you do, we=92ll send you seven of the most important, most profitable, and most unexpected secrets to profiting like a true real estate mogul =96 absolutely free! You can do this, even with no experience in real estate, even if you don=92t know the difference between a nail and a screw, and yes=85 you can even start profiting with real estate rentals if you have a few bumps and bruises on your credit report too! For information you can visit http://growrichwithrentals.com ...

Date format issue #2
Hello, I have date's that come to me in this format: 20040424 20040426 20040428 how can I convert them so EXCEL can read that as a date? Note: Excel does not recognize that format when you format/cells/number/date Thank -- Message posted from http://www.ExcelForum.com No, Excel will se it as text or as a number. You will need to transform it into a new column =Date(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "kkondrat1 >" <<kkondra...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

cannot send or receive #2
I could not open my office products yesterday so I did a reload of software. I am using my outlook email account now and it says the mail went through the send and receive but I still have mail in outbox and did not receive anyting. I have the account set up correctly and when I go out to my ISP's web site....the web email is there. my hotmail account works...just cannot get outlook to work. thanks ...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Converting Hours an minutes just into minutes #2
Is thier a formula to use to change hous and minutes, just into minutes ex: 01:30 (1 hour and 30 minutes) into 90 minutes. And how do you format the cells. Thank you Select the cell and change the format to: [m] -- Gary's Student "Six Sigma Blackbelt" wrote: > Is thier a formula to use to change hous and minutes, just into minutes ex: > 01:30 (1 hour and 30 minutes) into 90 minutes. And how do you format the > cells. > > Thank you ...

Email in HTML #2
Hello All, This is a strange one. We have a W2K server that is running a thir party application that needs to send out emails. I have it set to us one of our Exchange servers as the SMTP server. The app sends out emai it sends in HTML format. When I send the message to my Hotmail accoun it works fine. When I send it to my Outlook account it displays th HTML tags/code. If I forward from Hotmail to my Outlook it display properly. It's only when the app sends directly to a Outlook emai account that it displays improperly. Also, one user can recieve i fine, and if he forwards it to another O...

Find value for one cell from 2 different lists.
value for cell L55 when K33 has two options. K33 = Bags or sack and form that I need to get values from 2 different lists and a 3rd cell L13 has a volume to start from. I am using L13 as my starting cell. Formula is. =lookup(L13,A117:A191,H119:H191) for when K33 = sacks. and if K33 = bags. =lookup(L13,A117:A191,K117:K191). Regards Chris Sorry. How do I get it to work from both lists with one formula. Thanks Chris. "Chris" wrote: > value for cell L55 when K33 has two options. K33 = Bags or sack and form that > I need to get values from 2 different lists and...

Question #2
We are a land reseller company implementing Great Plains. We purchase a piece of land and divide it into smaller parts and resell it. At times, after selling the smaller piece of land, it is returned back and we need to resell it again. My question is: How do I implement this in Great Plains. If I consider the land we buy as an item, how do I split it into multiples. The other issue here is that if every land purchased will be a unique item, then our item data is going to grow very fast. Any ideas will be appreciated. Here are a couple of ideas: 1) When you create the Inventory Item...

Re: Floating Toolbar #2
Thanks for a quick reply, Bob I'm new to Excel, I've been looking were to put this code I've looked in the sheet, view code etc. Also looked in Help. I'm lost Could you tell me how and where to put this code. Application.Commandbars("myBar1").Visible = True ------------------------------- Hi All Is there a way to have a floating toolbar in say Sheet1 and one in Sheet2 with different names and they only show up in there own sheet. Thanks in Advance Dave ------------------------------- Hide it and make it visible in the worksheet_Activate event Application...

Cell Formula reference to cell Based On third Cell Content #2
I want to build a formula in one cell that calls a second based on the numeric value in a third. Specifically, Say the frst cell is F10. I want it to look up the number in F1, and then if F1=1, F10=E9*something if F1=2, F10=D9*something if F1=3, F10=C9* something etc. Hi! I notice that you use "etc" which means there are more. Exactly how many more? That number will determine the best way to approach this. Biff >-----Original Message----- >I want to build a formula in one cell that calls a second based on the >numeric value in a third. > >Specifically, Say...