Vlookup cells in combination of two columns (both in the lookup value AND table array)

I am trying to match information in multiple columns (lookup value) to
the same multiple columns in the table array.  If the combinations
don't match then I'd like the return to be #N/A.  I think an example
can better clarify what I'm trying to accomplish because I haven't a
clue whether I can do it...

On the lookup worksheet I have...
A1                 B1
12345            Closeout
12345            First Fill
22233            First Fill
22233            Closeout

On table array worksheet I only have one entry.
A1                 B1
12345            Closeout
22233            First Fill

I would like the return to be...
A1                 B1                  C1
12345            Closeout          Y
12345            First Fill           #N/A
22233            First Fill           Y
22233            Closeout          #N/A

I would like the lookup value to combine A1 and B1 and look for that
exact combination on the table array worksheet.

This may be very simple but I am stumped.

Any help is greatly appreciated

0
1/7/2007 3:54:30 PM
excel 39879 articles. 2 followers. Follow

3 Replies
518 Views

Similar Articles

[PageSpeed] 14

Insert a new helper column before column A of your table array so your data
is in columns B and C....then in A1 put this and copy down........=B1&C1

then for your lookup formula on the lookup worksheet, use this in C1 and
copy down

=VLOOKUP(a1&b1,'TABLE ARRAY'!A:C,3,FALSE)

Vaya con Dios,
Chuck, CABGx3


"Corrie" <corrie.oczkowski@pepsi.com> wrote in message
news:1168185270.904188.14600@42g2000cwt.googlegroups.com...
> I am trying to match information in multiple columns (lookup value) to
> the same multiple columns in the table array.  If the combinations
> don't match then I'd like the return to be #N/A.  I think an example
> can better clarify what I'm trying to accomplish because I haven't a
> clue whether I can do it...
>
> On the lookup worksheet I have...
> A1                 B1
> 12345            Closeout
> 12345            First Fill
> 22233            First Fill
> 22233            Closeout
>
> On table array worksheet I only have one entry.
> A1                 B1
> 12345            Closeout
> 22233            First Fill
>
> I would like the return to be...
> A1                 B1                  C1
> 12345            Closeout          Y
> 12345            First Fill           #N/A
> 22233            First Fill           Y
> 22233            Closeout          #N/A
>
> I would like the lookup value to combine A1 and B1 and look for that
> exact combination on the table array worksheet.
>
> This may be very simple but I am stumped.
>
> Any help is greatly appreciated
>


0
croberts (1377)
1/7/2007 4:29:13 PM
Wow.  That seemed to work.  Looking at what you suggested, I don't
understand the logic of it.  Would you mind explaining why having
another column with B1&C1 would help find what you are looking for in
two different columns?

Thanks!

CLR wrote:
> Insert a new helper column before column A of your table array so your data
> is in columns B and C....then in A1 put this and copy down........=B1&C1
>
> then for your lookup formula on the lookup worksheet, use this in C1 and
> copy down
>
> =VLOOKUP(a1&b1,'TABLE ARRAY'!A:C,3,FALSE)
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Corrie" <corrie.oczkowski@pepsi.com> wrote in message
> news:1168185270.904188.14600@42g2000cwt.googlegroups.com...
> > I am trying to match information in multiple columns (lookup value) to
> > the same multiple columns in the table array.  If the combinations
> > don't match then I'd like the return to be #N/A.  I think an example
> > can better clarify what I'm trying to accomplish because I haven't a
> > clue whether I can do it...
> >
> > On the lookup worksheet I have...
> > A1                 B1
> > 12345            Closeout
> > 12345            First Fill
> > 22233            First Fill
> > 22233            Closeout
> >
> > On table array worksheet I only have one entry.
> > A1                 B1
> > 12345            Closeout
> > 22233            First Fill
> >
> > I would like the return to be...
> > A1                 B1                  C1
> > 12345            Closeout          Y
> > 12345            First Fill           #N/A
> > 22233            First Fill           Y
> > 22233            Closeout          #N/A
> >
> > I would like the lookup value to combine A1 and B1 and look for that
> > exact combination on the table array worksheet.
> >
> > This may be very simple but I am stumped.
> >
> > Any help is greatly appreciated
> >

0
1/7/2007 4:51:48 PM
You were interested only in rows that had a specific value in column A,
matching with another specific value in column B....well, by CONCATENATING
the two columns together in the lookup array, the VLOOKUP formula only has
to search one column (which is all it can do) to find a row with the
combination you're looking for..........

hth
Vaya con Dios,
Chuck, CABGx3



"Corrie" <corrie.oczkowski@pepsi.com> wrote in message
news:1168188708.634122.325400@38g2000cwa.googlegroups.com...
> Wow.  That seemed to work.  Looking at what you suggested, I don't
> understand the logic of it.  Would you mind explaining why having
> another column with B1&C1 would help find what you are looking for in
> two different columns?
>
> Thanks!
>
> CLR wrote:
> > Insert a new helper column before column A of your table array so your
data
> > is in columns B and C....then in A1 put this and copy down........=B1&C1
> >
> > then for your lookup formula on the lookup worksheet, use this in C1 and
> > copy down
> >
> > =VLOOKUP(a1&b1,'TABLE ARRAY'!A:C,3,FALSE)
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Corrie" <corrie.oczkowski@pepsi.com> wrote in message
> > news:1168185270.904188.14600@42g2000cwt.googlegroups.com...
> > > I am trying to match information in multiple columns (lookup value) to
> > > the same multiple columns in the table array.  If the combinations
> > > don't match then I'd like the return to be #N/A.  I think an example
> > > can better clarify what I'm trying to accomplish because I haven't a
> > > clue whether I can do it...
> > >
> > > On the lookup worksheet I have...
> > > A1                 B1
> > > 12345            Closeout
> > > 12345            First Fill
> > > 22233            First Fill
> > > 22233            Closeout
> > >
> > > On table array worksheet I only have one entry.
> > > A1                 B1
> > > 12345            Closeout
> > > 22233            First Fill
> > >
> > > I would like the return to be...
> > > A1                 B1                  C1
> > > 12345            Closeout          Y
> > > 12345            First Fill           #N/A
> > > 22233            First Fill           Y
> > > 22233            Closeout          #N/A
> > >
> > > I would like the lookup value to combine A1 and B1 and look for that
> > > exact combination on the table array worksheet.
> > >
> > > This may be very simple but I am stumped.
> > >
> > > Any help is greatly appreciated
> > >
>


0
croberts (1377)
1/7/2007 5:44:54 PM
Reply:

Similar Artilces:

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

Conditional cell color.
I have used conditional formatting before in order to change the shading of a cell based on the cell value. However, I would like to do the same except have the color change based only on the alpha part of an alphanumeric value. Example: change shading to blue if the alpha = F, and change to yellow if = P (as in: F2409 & P1982). Thank you. Geza, use this formula: =SEARCH("f",A1). If you want it to be case-sensitive, use =FIND("F",A1). You know how to do the rest... -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com &...

How do I tell Excel to insert data from a cell 4 to the left?
Hi there, Here's the situation. I have a bunch of game prices in cells I8:I15. In cells B8:B15 are the names of these games. I have a cell that gives me the highest price [=MAX(I8:I15)] but instead of showing the price, I want it to show the name of the game. But I don't want to have to enter the name myself, I want Excel to do this automatically so if the prices change it can update itself. Is this possible? [Or does it even make any sense? ;)] Thanks in advance for any help (: Cheers, Jake. Try this... =INDEX(B8:B15,MATCH(MAX(I8:I15),I8:I15,0)) Note that i...

Combining Menus
Hello, I have two menus...I would like to merge both of them as follows: Menu 1: File View Windows Help Menu 2: "(Empty)" Mode MyView1 MyView 2 MyView 3 The Mode of Menu 2 should go under View of Menu 1. (These Menus are pre created, But only thing here is, two merge as mentioned above thru programmatically.) Any Ideas of combining Menus will be greatly appreciated... Regards, Chandra look at the CMenu class. use pMainFrame->GetMenu() to get the main frame's menu... probably use some other cwnd type fo...

Need One Excel Template to copy to TWO Databases
Here's the problem: Right now I have a one template (.xlt) and one excel database (.xls) that works fine. Now I want to create a second database so tha whenever I enter in information into the template, it creates a copy i both excel databases. I could just create a new duplicate database and a duplicate templat but that would require me entering in the information twice. Since enter in a lot of information, this is very undesirable. Anyone please help me figure out how to link one template (.xlt) to excel database files (.xls) Steps already taken: When the template wizard asked ...

How do you password protect a single cell or column of cells
I am trying to password protect a column of single cells but it keeps protecting the whole work sheet can ant one help? Select all the column not to be protected; Use Format | Cells | protection and deselect the Lock box Now Tools | Protect | worksheet Does this give you what you want? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Kinnaird" <Kinnaird@discussions.microsoft.com> wrote in message news:0D46DB91-B014-492A-94D8-24D5190CD154@microsoft.com... >I am trying to password protect a column of single cells bu...

Column help please
I have addresses in one column in an excel worksheet. When I highlite the address, it shows up correctly in the fx box (showing name on one line, address on the next, city, state and zip on last “line). However, when I pick the any address in the drop down box I made, the address shows up as one long line, not as the multiple lines I need. How can I have this show up as I need? Ignore this post, already figured out "galgolfer63" wrote: > I have addresses in one column in an excel worksheet. When I highlite the > address, it shows up correctly in the fx box (showing na...

Fields not showing up in columns
I have a field titled PageNumber in a table that is not showing up in my columns, although it is checked in the design view and is not hidden. On the form view I have access to this field and it has all the entries, which would indicate that nothing has been accidentally deleted. What's happened, and how can I fix this so the page number column shows up in the table. The terms you are using and the way you use them are confusing to me. Access has 'fields' in table. Those fields are displayed in columns. When you say 'I have a field titled PageNumber in a t...

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

Formatting Cell Fractions... HELP
Using Excel I formatted cells using fractions as quarters. how can I convert the 2/4 to 1/2 now? all of the numbers of my cells now show either 1/4, 2/4 or 3/4. I wanted to be able to change the 2/4 for 1/2 without having to format each one. Thank you Before I formatted the number showed 101.5, 102.8 - after formatting 101 2/4, 102 3/4. I was trying to find a way to change all the 2/4 for 1/2. Please don't multi-post You have an answer in your other thread -- Kind regards, Niek Otten Microsoft MVP - Excel "Twicebest" <Twicebest@discussions.microsoft.com> wrote...

Controling how zero values are plotted in charts
Can anybody advise me how to overcome the following: I am trying to create a chart whereby the source data is calculated from a formula. I do not want zero values to be plotted in the chart. I have tried the following, going to TOOLS, OPTIONS, CHARTS & then selecting 'plot empty cells as' = Not Plotted(leave gaps), but this does not overcome the problem. This function only seems to work when data is not related to a formula? I would be very grateful for any help that anybody can give Kind Regards Paul Paul: This is a very frequent question. You are half way to your answer. ...

Merging cells #4
I am using an existing worksheet in Excel. Some cells have already been merged. I need to merge 2 more cells and i am unable to accomplish that. The "merge/center button" on the toolbar is not highlighted, so i am unable to merge the needed cells. Any suggestions? Any chance your worksheet is protected? brownk wrote: > > I am using an existing worksheet in Excel. Some cells have already been > merged. I need to merge 2 more cells and i am unable to accomplish that. > The "merge/center button" on the toolbar is not highlighted, so i am unable > to m...

colorize celle from a range of date
www.gssitaly.com/calendar.zip This is a simple planninig of vacation i would want a macro assigned in to a botton in the sheet FERIESA_FATTE that make: click on the botton and automaticly from every line of the sheet FERIESA_FATTE it colorized the 2 other sheet in base of the month example: 36755 6500 D'AGOSTINO NICOLA have arange 02/01 05/01 colorozie in red with a little border the sheet PRIMO_SEMESTRE range E6:H6 have arange 15/03 colorozie in red with a little border the sheet PRIMO_SEMESTRE range BZ6 have arange 14/07 16/07 colorozie in red with a little border the sheet SECONDO...

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

Splitting a cell???
I have a long list of data combined in once column by cell. The data in each cell includes a phone number and address. I would like to split that data into two different cells. Is this possible and if so how? The data looks like this: (555) 555-5555 999 Broadway, Apt A etc. etc.. etc... Any advise? Brw Try <Data> <TextToColumns> Check "FixedWidth", <Next> Read the directions to move the break line to the end of the phone numbers and eliminate any others, Then <Finish> -- HTH, RD ============================================== Please keep all corr...

Trend Line constants as Excel Cell Values
I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: > I want to put the constants of a 2 degree polynomial trend line into Excel &g...

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

How do I link one person to another in the same table?
I have a table of Contacts. Some of my contacts know other contacts, and I would like to indicate that in their record. However, for Data Type, I cannot create a drop down list of contacts from within the table to choose from. Any idea how to to this? Or, if this is not possible, can you suggest another way to indicate "who knows whom"? Your contact table ought to look like: TblContact ContactID FirstName LastName etc Then you need am acquaintenances table: TblAcquaintenance AcquaintenanceID ContactID (identifies a Contact) AcquaintedContactID (Ident...

Column width and pasting sections in excel? Formatting questions
I am trying to make a spreadsheet in which I need to have varied column widths in different sections, one under another. (they don't need to relate directly, and no major equations going on) for example: xxxxxxxxx|xxxxxxxxxx xxxxxxxxx|xxxxxxxxxx xxxxxxxxx|xxxxxxxxxx xxxxxxx|xxxxx|xxxxxx xxxxxxx|xxxxx|xxxxxx How do I go about splitting the sheet or whatever I need to do so I can manipulate columns differently based on the row I'm in? Thanks! Rich Column widths apply to the whole column. You may be able to use merged cells to give the appearance that you want, but I try to stay...

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

Combine fields containing quotes
I am trying to combine several fields into one. Currently my formula looks something like this =D3&"Some text "&E3&" <br><b>more info: </b>"&F3 This all works just fine, except sometimes I'll need to insert html more advanced than a simple bold or break. How do I escape the quotes in my html so they don't affect the formula? =D3&"Some text "&E3&" <br><b>more info: </b>"&F3&Q3 For now I have just simply added my html in another field and appending it this wa...

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

Scatter Chart with non-numeric values on X axis
Hi, I'm trying to figure out how to create a scatter chart (graph) wit words for the x axis. I'm trying to setup a graph showing availabilit of servers, so for the Y axis I want to show a percentage (betwee 1-100%), and for the X axis, I want to show the name of the servic (mail, news, etc). However, it seems that with a scatter chart I ca only do numeric values on both axis. Is there any way around this? Thx -- penas ----------------------------------------------------------------------- penasm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View...

Referencing relative cell
I'm am trying to reference a cell one row up and three to the right to check if it is zero and I get a formula error. Can someone help with this? Thanks. Michael =IF(R[-1]C[+3]>0,AG5*E4,0) Michael You can't mix A1 and R1C1 reference styles. You have to use whichever one is active in the Tools>Options>General tab If you are using A1 style you will need to enter the actual cell reference. If you're not familiar with Absolute and Relative reference styles you can read up on them in the Help files Good Luck Mark Graesse mark_graesser@yahoo.co ----- Michael wr...

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