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 not
necessarily all the same stock codes are in each file and neither file
has a complete listing of all stock codes. Where the stock code is the
same, I want to insert the quantity from file 2 into file 1 so that I
can compare the two.

If anyone can help I will be eternally grateful.

Thanks in advance



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/23/2003 6:19:22 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
690 Views

Similar Articles

[PageSpeed] 30

Hi Bruce:

You question is a little confusing.  You use "Column A" at 
one point and then "Column 3" at another.  But I think you 
want a function like VLookup.  The function is something 
like (you put it into column 3 of sheet 1):

=vlookup (sheet1!a1, sheet2!b1:c100, 2, False)

But actually, I think you are better off moving everything 
in Access.

If you want, I can email you an excel file with fake data 
that does the work, assuming that I understood the 
question correctly.

Dom


>-----Original Message-----
>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 not
>necessarily all the same stock codes are in each file and 
neither file
>has a complete listing of all stock codes. Where the 
stock code is the
>same, I want to insert the quantity from file 2 into file 
1 so that I
>can compare the two.
>
>If anyone can help I will be eternally grateful.
>
>Thanks in advance
>
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~ View and post usenet messages directly from 
http://www.ExcelForum.com/
>
>.
>
0
10/23/2003 6:51:41 PM
One way:

In sheet1:

C1:     =VLOOKUP(A1, Sheet2!B:D,3,FALSE)

where 3 corresponds to the third column in the range B:D, or column 
D's value.

In article <Bruce.vrlba@excelforum-nospam.com>,
 Bruce <Bruce.vrlba@excelforum-nospam.com> wrote:

> 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 not
> necessarily all the same stock codes are in each file and neither file
> has a complete listing of all stock codes. Where the stock code is the
> same, I want to insert the quantity from file 2 into file 1 so that I
> can compare the two.
>
0
jemcgimpsey (6723)
10/23/2003 6:56:40 PM
Reply:

Similar Artilces:

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 ...

Question about IF function
I would like to have every cell in column A that has value of "0.00" be changed to "NO" and every cell that is > 0.00 to have number values changed to "YES". What would the formula be? Thanks in advance. Not sure why you're thinking IF() function. If you just want to display "YES" or "NO", choose Format/Cells/Number/Custom and enter: "YES";;"NO";@ Otherwise In article <12750bd4-e150-4c25-92ca-2b06e540f525@t3g2000yqa.googlegroups.com>, excel_21 <lin.jeff.21@gmail.com> wrote: > I would like...

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...

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...

Need offset function help, I think
I have 10 products rows with a cost in column BW of rows 21 thru 30, i.e., cells BW21 down to BW30. Those 10 products have the # of units sold each month, for 22 months, shown in rows 41 thru 50, in columns E thru Z, so column E is month #1 ... col Z is month #22. The total range is cells E40 thru Z40. There is a cost factor vector that has six factors in row 70, cells G70 thru L70. These are to be used =91for all time periods=92 and for all products. Any time there is a sale of a product in a certain month, I want to enter six months of cost associated with producing the item...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

graphics converter not working
I am using Publisher 2000 to create a newsletter. It has been going well but now that I'm ready to insert pictures from files, it won't work. I get the message saying that Publisher can't convert this picture (it's either that it doesn't recognize the format or there was an error installing the graphics converter and to go to to Start > Control Panel > Add/Remove Programs > Highlight the Publisher program and choose "Change". When prompted, choose "Add Features". Then be sure to select to run ALL from your computer so that everything is...

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...

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...

Microsoft Office Outlook has stopped working
Outlook 2007 has been installed for all of 6 days. Today it stopped responding. I can no longer run Outlook 2007. Every time I open it I get "Microsoft Office Outlook has stopped working" I do the Check online for a solution and close the program, and the program just closes. I have run Microsoft Office Diagnostics and no errors. Scanpst and no problems. I am running Vista Business and Office 2007. I hate I am going to have to go back to XP Pro and Office 2003, but this is unbearable. My problem is I copied all my emails to Outlook 2007 so it is no longer a 200...

SumIf Function #3
I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay Jay look at SUMPRODUCT Look at the Help and search the Archives for examples Regards Trevor "jayceejay" <jayceejay@discussions.microsoft.com> wrote in message news:39CB49F6-4459-4A4B-8856-E3E8BE615FFB@microsoft.com... > I'm...

CSV import into table not working...?
I'm trying to use this command to import a CSV file into a table... DoCmd.TransferText acImportDelim, "RetSpec", "tblRet", "\\drake\subcon \ret\data\ret.csv", False it never does it...any ideas? If I do a straight import using those Specifications "RetSpec" tblRet is poppulated with the data from the CSV file without any problems. Right above the DoCMD i have: On Error GoTo BadImport and BadImport is: BadImport: MsgBox "Ret data was NOT imported!!", vbOKOnly, "File Import Problems" Exit Sub Any ideas? I always get my...

OWA Stopped to work
When I am trying to open OWA it shows the following: 10.0.0.2 - /exchange/ -------------------------------------------------------------------------------- [To Parent Directory] 6/22/2005 9:47 PM <dir> Administrator 6/22/2005 9:47 PM <dir> emailcim 6/22/2005 9:47 PM <dir> iroda 6/22/2005 9:47 PM <dir> SERVER-SA 6/22/2005 9:47 PM <dir> SystemMailbox{AD0D15B0-8FFA-4CBA-AB2C-68064F07AEDB}insteed showing it's own interface. Can anybody help me? ...

CRM Functionality does not appear in Outlook
Hi all... Hope someone can help here - I am a non-technie looking after the install of CRM for a small sales organisation. I have succesfully installed the outlook client on all laptops but on 1 desktop I am getting a problem. I have installed all pre-requisites and passed all pre-install checks, and the installation appears to go smoothly. However, the additional functionality does not appear in outlook. No error codes are generated so I can't be more specific than this I'm afraid, any ideas would be greatly appreciated. Cheers Holmesy in outlook go to tools options..then get t...

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" ...

IF Function Problem
Hello All, I am using Office XP and have the following problem I have a worksheet which uses lookup to extract names from range name Codes as follows Col O=IF(ISNA(VLOOKUP(D2,CODES,6,FALSE)),"",(VLOOKUP(D2,CODES,6,FALSE))) I wish to add another IF condition to the above formula to test that: If Col D2 = EGSP AND Col A = date greater than or equal to 15-Nov-2005 then it should display the name as 'Jack' otherwise 'James'. All other values should remain unchanged Thanks a lot Rashid =IF(D2="EGSP",IF(A2>=--"2005-11-15","Jack",&q...

Ex55 Mailbox Manager not working
Hi! We have the problem that the Exchange 5.5 Mailbox Manager is not cleaning the mailboxes! In the EventLogs it is said that 0 items are processed but no errors at all! We are running Exchange 5.5 SP4 on W2K SP4. We also tried the Post-SP4 fix for mailbox manager but it doesn't help! It seems that after restarting the system service the mailbox manager is running for that day. Are there any other solutions or fixes that might help available? Thanks for the help! BR, Jochen Corey, what additional patches do you have installed on your Exchange server? Maybe it is somehow connect...

Consistent function of Utilities logon screen when applying hotfix
When our users attempt to install a hotfix roll-up, when they are logging into Dynamic Utilities for the first time, the user interface is very touchy. What I mean is that they must click the username field and then type. Then, they must use the mouse again to click the password field and type. Then, they must click the OK button. If they try to use backspace, enter key, tab key, etc., extra characters are inserted. The only way that they can "edit" their username and password information during logon is to use the mouse to highlight and the type over the information. ...

working with excel and other program
hi, We basically feed the data in excel sheet from other application (business objects) . The data that we enter is purely numbers. however we need to move up and down between two applications. To move in Business objects i have to click in the application and then agin to enter that data in excel i have to click in excel sheet.............we enter huge amout of data and this is time consuming. could u please let me know if it is possible to move in both the application without clicking . In other words can we have two application active at the same time, may be in mouse hover can...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

How to call a non static function from a static function
Hi I need to go to a non static function from a static function?can anybody suggest me how to do it?any kind of help will be greatly appreciated. You need to pass the object whose function you want to call. static void s_foo( A* a ); class A{ public: A(); ~A(); void funcA(); }; main() { ... A a; s_foo(&a); } static void s_foo( A* a ) { a->funcA(); } -Seetharam ...

Calculating turaround time using IF function
I'm just learning to use the IF function, and it is not calculating correctly, so am looking for help. I'm using Excel 2000. For my spreadsheet, if a document is made available after 1700 hours or before 0700 hours the following day, I want it to calculate the turnaround time for those reports to begin at 0700 hours; otherwise calculate the remainder of the reports from the time the report became available to completion. Here's the IF statement I'm attempting to use with little success. E=Time report available I=Finish time =IF(OR(E>"17:00:00",E&l...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

Rules not working consistently #2
Ok, Outlook 2003. I am getting a ton of junk mail for various ED medications (you know the medication that comes in a blue diamond shaped tablet) (see the last paragraph of this post). So I created a "rule" to delete any incoming message with in the subject. But the rule works inconsistently. Most such messages do get deleted, but not all of them. And I can't figure out why it's inconsistent. There is nothing wrong with rule; if I go into the Junk Mail Folder, and I "run rule now", it gets rid of those messages in the Junk Mail Folder with the medication&...

Disable COPY function
Does anyone know how can I avoid/restrict people Copying-n- pasting data from my excel spread-sheet. I am trying to lock down an excel spread-sheet to "Read Only" - in its true sense and dis-allow even copying data from this sheet to any other. Basically I want to turn off the COPY function. Secondly, if a person saves this sheet locally under another file name, how would I still disallow the COPY function. Is there any VBA module (which I can later password protect) which takes care of this. I do not have admin rights to set folder/file server permissions and the hiararch...