Two-column lookup help!

Greetings,

I am having difficulty looking up an item using two-column lookup
formulas as suggested.  Following are the parameters of my situation

2 Different Workbooks

Workbook 1 (Reference Workbook)

Column      b      c                     d       
           125     Lay Foundation        March 9, 2004 
           125     Purchase Frame mat.   March 27, 2004
           125     House complete        May 15, 2004
           267     Lay Foundation        June 5, 2004
           267     Purchase Frame mat.   June 28, 2004
           267     House complete        August 29, 2004     

Workbook 2 (Extracted information)
 
 Column    b      c                                 d
           ID #   Date of completion for Process A  Date of comp. for
Process B etc. etc.

Up to this point, I have tried many variations of the following
formula to make things work (assuming ID# is in row B1):
   =INDEX(WORKBOOK 1 D:D, MATCH(B1&"Lay Foundation", Workbook 1
B:B&Workbook 1 C:C,0))

 This was also of course done with Ctrl, Shift, Enter (array formula)

My question is simply how can I solve this?  I get back the error
message #NUM everytime.  I am quite fearful that two-column lookups
might not be possible when referencing a second workbook.

Would be greatly appreciative for any help I could receive!  

Thanks,

Eric
0
ostmoeep (1)
6/17/2004 2:15:19 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
540 Views

Similar Articles

[PageSpeed] 42

When I have done this kind of thing before I have used a helper column to concatenate the data from the two key columns. Insert a new column B on your sheet1 containing the data to be looked up. Enter in B1: C1&" "&D1. This should provide a result like "125 Lay Foundation". Then you can do a VLOOKUP on your results page like:

=VLOOKUP($B1&" Lay Foundation", Sheet1!$B$1:$B$100,4,FALSE)

In the first argument ($B1&" Lay Foundation") you are reconstructing the concatenated data that your lookup table is keyed on. The second argument (Sheet1!$B$1:$B$100) is the range reference for your lookup table. The third argument (4) says return the data in the fourth column of the lookup range, and the fifth argument (FALSE) says look for an exact match.

If you head up your columns with the Process name, you can change the first argument to something like $B1&" "&A$1, which is a formula you can use to fill the entire output range.

"EKnight" wrote:

> Greetings,
> 
> I am having difficulty looking up an item using two-column lookup
> formulas as suggested.  Following are the parameters of my situation
> 
> 2 Different Workbooks
> 
> Workbook 1 (Reference Workbook)
> 
> Column      b      c                     d       
>            125     Lay Foundation        March 9, 2004 
>            125     Purchase Frame mat.   March 27, 2004
>            125     House complete        May 15, 2004
>            267     Lay Foundation        June 5, 2004
>            267     Purchase Frame mat.   June 28, 2004
>            267     House complete        August 29, 2004     
> 
> Workbook 2 (Extracted information)
>  
>  Column    b      c                                 d
>            ID #   Date of completion for Process A  Date of comp. for
> Process B etc. etc.
> 
> Up to this point, I have tried many variations of the following
> formula to make things work (assuming ID# is in row B1):
>    =INDEX(WORKBOOK 1 D:D, MATCH(B1&"Lay Foundation", Workbook 1
> B:B&Workbook 1 C:C,0))
> 
>  This was also of course done with Ctrl, Shift, Enter (array formula)
> 
> My question is simply how can I solve this?  I get back the error
> message #NUM everytime.  I am quite fearful that two-column lookups
> might not be possible when referencing a second workbook.
> 
> Would be greatly appreciative for any help I could receive!  
> 
> Thanks,
> 
> Eric
> 
0
Vaughan (64)
6/17/2004 2:37:02 PM
Reply:

Similar Artilces:

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

synchiing two computers?
Is there a way to synch Outlook on two computers? I end up with some email on my laptop and some on my desktop - it would be nice if the two could be synched so that I could have all of my email on both computers. Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Bill Andersen asked: | Is there a way to synch Outlook on two computers? | I end up with some...

How to turn on/off Column Sort Shading
My wife and I both have Outlook 2003. But in her Inbox, the Received column, which is the column by which the messagees are sorted is shaded slightly gray, so it stands out from the others. In my inbox, all columns have exactly the same white background color, even though I also sort by "Received". If she switches to sorting by another column, that one becomes the one shaded in gray. I cannot figure out why hers is different from mine. How does this "shading for sorted column" feature get turned on..or off? thanks in advance, Rob from AZ Are you using the same windows c...

Help with Function Macro
Hi Julie / Anyone else who can help Sorry that my earlier post wasn't very clear - this stuff really boggles my brain. OK, so here is what I have to do: I've got a template into which employee evaluations are added, a figure of 1,2,3,4, or 5 (1 being poor - 5 being excellent). These are added into Column C from Row 12 downwards. Cell D2 is Overall Evaluation which is an average of all evaluations entered. Cell D3 is Recent Evaluation which is an average of the 5 most recent evaluations entered. I have entered a formula into Cell E5 to show the Quick Review which is =Review(d2,d3)...

Pls help to find number of days for ageing report
if the cell A1 = 30-Nov-2009 and cell b1 01-Nov-2009 How I can find the number of days between that date. It is for ageing analysis for customers Pls help It's simply: =a1-b1 Format as a number Regards, Fred. "pol" <pol@discussions.microsoft.com> wrote in message news:3FBCD269-858B-40DA-907C-D3D5B5053D85@microsoft.com... > if the cell A1 = 30-Nov-2009 and cell b1 01-Nov-2009 > > How I can find the number of days between that date. It is for ageing > analysis for customers > > Pls help > Use Datedif function. =DAT...

Converting A String to a Column of Data
I have some strings like this (some are as long as 4000 separated values): A;AA;AAI;AAP;AAPL I am looking for a way to convert these strings into column data like this: A AA AAI AAP AAPL Thank you in advance. Data - Text to Columns, delimited by semicolon. Select Data, copy, paste special - transpose. -- Best Regards, Luke M "carl" <carl@discussions.microsoft.com> wrote in message news:E828806F-40A2-4C85-8D01-D55BD8ED28BB@microsoft.com... >I have some strings like this (some are as long as 4000 separated values): > > A;AA;AAI;AAP;AAP...

Compare two cells in two different files and return answer
I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact mat...

help with pervcentages
Walker YES at he end of the line I need to show yes and no in percentages, and as I add additional clients this percentage changes Thanks in advance Cook YES Vailes NO Coleman YES Correa YES Leavitt NO Branch YES Forbes NO Walrath NO Bryant YES Do you mean this =COUNTIF($B$1:B1,B1)/COUNTA($B$1:B1) formatted as percentage? -- __________________________________ HTH Bob "Edgar G" <EdgarG@discussions.microsoft.com> wrote in message news:59AD3389-9931-42F2-BC91-3A8A666526AB@microsoft.com... > Walker YES > at he end of the line I need to show yes and no in p...

Pivot table for two different data variables
I have the following table Name type Cost overhead amy C1 23 12 bob C2 129 17 cat C2 36 45 doo C2 100 32 I owuld like to create a pivot table that looks similar to: the standard pivot table. (Please ignore type for now.) name Data Total amy Sum of overhead 200 Sum of cost 12 bob Sum of overhead 130 Sum of cost 23 cat Sum of overhead 90 Sum of cost 19 doo Sum of overhead 87 Sum of cost 34 Total Sum of overhead 507 Total Sum of cost 88 But with overall total (overhead + cost) for ...

comparing columns
I have a master Excel worksheet, which has six columns:- C:D:E:F:G:H and there are approximately one thousand rows. On a daily basis I import data into Column A. of this master worksheet. The data consists of lots of different 10 digit numbers, so I have a different 10 digit number in each row of Column A., an example of which follows:- A 1234567896 1721626196 3333445556 7768754321 9675332699 The amount of rows that I import also varies daily, it can be as low as five rows of 10 digit numbers imported, or approximately 999 rows of 10 digit numbers imported. The rest of th...

COUNTIF (?) on two criteria
I have a spreadsheet that logs work activities and records team members' completion of project units. Column B records team member names. Column C has project unit status - 'pending', 'beta' or 'completed'. It's easy to set up a formula to count how many units are allocated to each team member - =COUNTIF(B1:B1000,"Joe"). But what I need to do is set up formulas to count how many units Joe has completed, or has pending or at beta, and this means assessing data in both columns - which COUNTIF won't do. This doesn't strike me as an especially...

Two IF Statements with two answers
In cell J9 I need a formula that would do the following: If G9="Yes" then F9, If G9="No" then -f9 Note: G9 has a drop down list with Yes, No answers. If there are no other alternatives that yes and no =IF(G9="Yes",F9,-F9) otherwise =IF(G9="Yes",F9,IF(G9="No",-F9,"") Regards, Peo Sjoblom "heater" wrote: > In cell J9 I need a formula that would do the following: > > If G9="Yes" then F9, If G9="No" then -f9 > > Note: G9 has a drop down list with Yes, No answers. > =IF(G9=&qu...

Lookup (more complicated)
Can someone help me figure out the way to handle this. Four columns of data 11 rows of data Column A = {A,B,A,C,D,A,B,B,C,D,A} Column B = {Y,N,Y,Y,N,N,N,Y,Y,N,Y} Column C = {1,2,4,7,5,3,5,2,1,6,8} Take the last row (#11) of data as an example = {A,Y,8} Column D is where I need to get the formula to return this: Show me the last value in Column C where Column A="A", and Column B="Y" In this example, that value would be the 3rd row which is 4. F Tahbaz wrote: > Can someone help me figure out the way to handle this. > > Four columns of data > 11 rows of data ...

Two axis
Positioning the Vertical axes: I have a chart where there are two axes. The first implementation produced the two scales on the right side, one inside, the other outside the chart area. Unusual, but luckily it fits well the chart. I tried to duplicate the feat with another dataset (similar in structure) and did not even get the second scale to show right, left or center (data plotted with two different scales allright). Where is the info to control these functionalities? Tx, Pierre On Wed, 29 Oct 2003 17:48:13 +0100, pl.carry <pl.carry@wanadoo.fr> wrote= : > Positioning the Vertic...

Help with hidding a column in Excel
I need to hide a column in excel from access. How can I do that? Alaa "Al" wrote: > I need to hide a column in excel from access. How can I do that? > Alaa Hi Al, one of the nice things about Excel is that you can record a macro and then inspect the code. I recommend that this is the method that you use to learn what you want to do in this case. Then just copy the code into your Access routine. You did not ask how to set a reference to Excel and then open a workbook. So I assume you know how to do this. If you're not sure, then this site has a good search engine to...

Filling in empty cells in columns
Dear All I have a problem could somebody please help? I have 3 columns of data A,B,C. A nd B are full but not all the cells in C are filled out. How do I make the cells in C that are empty equal the value in B without having to do it manually and still keeping the existing values in C? Please help Much Appreciated Andrew PS Thank you to whoever helped me last time I didn�t get a chance to say that. -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28639 View this threa...

plot two groups of numbers on one series of xy coordinates.
how to create a plot from separate x and y values. i.e. y values do not correspond to the same set of x values. example x1= 5, y1=10; x2= 8, y2=15 I can read your question 2 ways; A) you data looks like this x y 5 10 8 15 etc. Select the data and make an XY chart B) you have two sets of data x1 y1 5 10 10 12 17 15 21 20 and x2 y2 3 8 7 14 12 19 18 24 Make an XY chart of the first data series Select all the data (including headers) for second set, and use Copy Click the chart to activate it Use Edit | Paste Special .... indicate New Ser...

two variables
I have a query that asks for collcode, collcode2 and booth. On any given day an employee may be collcode or collcode2. I would like to know how many times a certain emp has been paired with another employee. So how do I ask if an employee has been collcode/collcode2 and who the other person was? I hope this question makes sense. Thanks for any help Query one: SELECT CollCode, CollCode2, Booth FROM YourTable UNION ALL SELECT CollCode2, CollCode, Booth FROM YourTable Query Two SELECT CollCode, CollCode2, Count(CollCode) AS TIMESPAIRED FROM QueryOne GROUP BY CollCode, CollCode2 IF y...

Find two PCI instance but load Init() one time
Hi all, I'm developing a stream interface driver for my PCI device under WinCE 6.0 and this driver works very well currently. Now, I got a problem when testing two the same PCI devices with my stream driver. The registry can find two PCI instances (i.e. PCI\Instance\mydrv1, PCI\ Instance\mydrv2) but system only call my XXX_init() one time. Does anyone know what the problem is? The following information is my registry setting: ================================================================== [HKEY_LOCAL_MACHINE\Drivers\BuiltIn\PCI\Template\mydrv] "Dll"="myd...

Charting based on a specific month in a column
Hi all, I would like to plot a bar chart that plots the bar based on the sum of a column for a particular month. For example: A1 Date A2 1/5/09 A3 2/5/09 A4 1/6/09 B1 Qty B2 10 B3 10 B4 5 I would like to plot a bar chart that on the bottom axis has the months i.e. May, June and then sums up column B and plots the sum of the figures against the months i.e. May - Qty 20, June - Qty 5 etc. I appreciate any assistance you can give me. In D1 enter text "date" in E1 enter text "Qty" In D2 enter date for Jan 1 (1/1/2009) and in D3 date for 1 Feb 2009 (looks like you woul...

Copy selected columns from .xls to a .txt file question
I would like to be able to copy two columns from a .xls file, columns 2 (B) and 9(I) into a .txt file for approximately 100 rows, using VBA. What is the best way to do this? Thank you try this Sub Copy_Rows() With Application .DisplayAlerts =3D False .ScreenUpdating =3D False End With FpatH =3D "C:\Documents and Settings\username\filename.xls" Workbooks.Open FpatH FileP =3D ActiveWorkbook.path Range("B1:C100").Copy Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs Filename:=3D _ FileP & "\" & "1.txt&...

Exchange 2003 allowing spoofed identities, please help.
Well... my exchange 2003 server is allowing spoofed identities (within the same domain) from an external SMTP connection. example: HELO MAIL From: <chrisl@mydomain.net> RCPT To: <johndoe@mydomain.net> DATA From: chrisl@mydomain.net Subject: email problem test - delete To sum up, I can send an email as myself to anyone in my company without being asked for authentication from any connection. How do I fix this? I've tried unchecking "allow anonymous connections" in the smtp tab, but as I suspected, that disables all incoming email from the world. Any help wou...

2 Column Counts & Display
I have in field "Nature" set two columns to be looked into and presented in a combo box, Column 1 showing the Account ID and Column 2 the Account Title. However, when I select an entry only the first column, i.e. Account ID is displayed. I want the entry to be either showing both or the second column's relevant entry i.e. either: Account ID | Account Title or Account Title Any suggestions/guidance? -- Thanx in advance, Best Regards, Faraz Hi Faraz , A combo box that is not in dropped down mode will only display the first visible column. The easiest way ...

Comparing two lists for matches
Hi, I am trying to do the following: There are two sets of supplier lists - A List and B List. I need to compare the two lists. Source A is my master. Source B is a subset of that but has contact information for all the suppliers. Hence, my goal is to retrieve the contact information from Source B and match it to the suppliers of Source A. The above can be easliy achieved using a vlookup funtion. But the problem is that some of the supplier names in the two lists are not exact matches. Hence, vlookup does not catch it. For instance, a supplier is listed as ABC Inc. in A list and as ABC, inc....

Compare two lists of names
Can you compare two lists of names where one list is last name first and the other is first name last? try this formual: =RIGHT(TRIM(E2),LEN(E2)-FIND(" ",E2))&" "&TRIM(LEFT(TRIM(E2),FIND(" ",E2))) it will convert First name last name to last name, first name or Visa versa. then you can compare your lists. "ea" wrote: > Can you compare two lists of names where one list is last name first and the > other is first name last? ...