VLOOKUP Across Two Columns

Hi all,

I'm trying to use VLOOKUP to populate a number of cells. However, fo
my cells to be filled I need VLOOKUP to look at two columns, rathe
than one. 

The reason for this is that the spreadsheet is dealing with a number o
teams in the business. Within the teams there are a number of roles tha
have identical job titles in them. For example, I have a team calle
'Services' and one called 'Marketing' within both these teams there i
a 'Project Manager' and a 'Business Analyst'.

So, what I need Excel to do is first look at the team, then look at th
job title, and from that VLOOKUP in the rest of my spreadsheet t
populate the cells. I've fiddled around with a few IF and AND formulas
but not joy.

Any ideas?

TIA,

Samuel

--
Samuel
-----------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2750
View this thread: http://www.excelforum.com/showthread.php?threadid=49872

0
1/6/2006 3:50:43 PM
excel 39879 articles. 2 followers. Follow

2 Replies
412 Views

Similar Articles

[PageSpeed] 12

=INDEX(Range3,MATCH(1,(Range1="team")*(Range2="jobtitle"),0))

entered with ctrl + shift & enter

where Range3 is the range where you want the result returned from

-- 

Regards,

Peo Sjoblom

"SamuelT" <SamuelT.217zxm_1136562900.309@excelforum-nospam.com> wrote in
message news:SamuelT.217zxm_1136562900.309@excelforum-nospam.com...
>
> Hi all,
>
> I'm trying to use VLOOKUP to populate a number of cells. However, for
> my cells to be filled I need VLOOKUP to look at two columns, rather
> than one.
>
> The reason for this is that the spreadsheet is dealing with a number of
> teams in the business. Within the teams there are a number of roles that
> have identical job titles in them. For example, I have a team called
> 'Services' and one called 'Marketing' within both these teams there is
> a 'Project Manager' and a 'Business Analyst'.
>
> So, what I need Excel to do is first look at the team, then look at the
> job title, and from that VLOOKUP in the rest of my spreadsheet to
> populate the cells. I've fiddled around with a few IF and AND formulas,
> but not joy.
>
> Any ideas?
>
> TIA,
>
> SamuelT
>
>
> -- 
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=27501
> View this thread: http://www.excelforum.com/showthread.php?threadid=498725
>


0
terre081 (3244)
1/6/2006 4:15:50 PM
You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
   match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

SamuelT wrote:
> 
> Hi all,
> 
> I'm trying to use VLOOKUP to populate a number of cells. However, for
> my cells to be filled I need VLOOKUP to look at two columns, rather
> than one.
> 
> The reason for this is that the spreadsheet is dealing with a number of
> teams in the business. Within the teams there are a number of roles that
> have identical job titles in them. For example, I have a team called
> 'Services' and one called 'Marketing' within both these teams there is
> a 'Project Manager' and a 'Business Analyst'.
> 
> So, what I need Excel to do is first look at the team, then look at the
> job title, and from that VLOOKUP in the rest of my spreadsheet to
> populate the cells. I've fiddled around with a few IF and AND formulas,
> but not joy.
> 
> Any ideas?
> 
> TIA,
> 
> SamuelT
> 
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501
> View this thread: http://www.excelforum.com/showthread.php?threadid=498725

-- 

Dave Peterson
0
petersod (12005)
1/6/2006 4:16:48 PM
Reply:

Similar Artilces:

Splitting Text from single cell in column across multiple Columns
Text To column wont work because I have name and address info in a single cell in this format. Each cell has different data representing different addresses. Name St # St Name Phone Number City, State zip I want a formula or something to take first line and put in one column, the 2nd line in another column and the 3rd line spread across 3 columns. Although if you could just find a way to split each line into a column, that would work. Once again, Text to colun won't work, I have multiple cells with diffeent addresses, they are not all the same and there are no deli...

Separating First Name(s) and Last Name into Separate Columns
I have a spreadsheet with one column ("name") that contains names in the following formats occuring randomly down the list: Firstname Lastname Firstname & Firstname Lastname Firstname Lastname & Firstname Lastname I need to get them all into two new columns "firstname" and "lastname" and I'm having difficulties writing a formula to account for all 3 scenarios in one cell. (If I have to do it in multiple steps - so be it!) For the 1st example, the contents of the new "firstname" column should be Firstname, while the "lastname"...

Keep the exact same workSHEET, in two seperate workBOOKS.
This is probably something really easy, but I don't know how to do it. I currently maintain 2 different worksheets for statistical purposes. 1 for my department, which keeps data on a daily, weekly, fortnightly, and finally yearly (by week) basis. This is MailStats.xls. I also maintain the yearly (by week) version for the other 2 departments, as well as the summaries. This is WorkStats.xls Now, the yearly (by week) sheet is the same in both workbooks. I simply finished off designing WorkStats.xls and copied my departments worksheet (SGIC_GIS) to MailStats as SGIC_GIS_2004. The data goi...

3D Surface from data from 3 columns
introduction: I need 3D surface chart made in Excel. I have data written in 3 columns lets say A,B,C I think that I need to convert them to table to make the chart (A would be horizontal axis, B- vertical and C- values)- but I do not know how to do it :-( Could you please help me? :-) Jack Hi Jack have a look at http://www.tushar-mehta.com/excel/charts/3d_surface/index.html Frank jack wrote: > introduction: > I need 3D surface chart made in Excel. > > I have data written in 3 columns lets say A,B,C > I think that I need to convert them to table to make the chart (A >...

Instalation of CRM on two place
Hello Our company got project for CRM 3.0 and we will install in next week. Our consulent demands that i must installed same crm at one of our local computer because they will work localy then export the changes and import at customer CRM server. My question is If i installed CRM at customer's network AD and take backup for SQL databases and restore thats on the local Server and connect to CRM or must install new CRM with same Lisence? Help me. Consulent wants same invirment as our customer has for CRM. -- Maikal You can't simply take a backup of a database and restore it i...

special type of rows to columns procedure
This is how i have the data column1 column2 column3 column4 row1 1 2 3 4 row2 5 6 7 8 row3 9 10 11 12 And this how i would like to transform it with the most automated way i can. column1 column2 column3 column4 row1 1 row2 2 row3 3 row4 4 row5 5 6 7 8 row6 9 10 11 12 Which means that i'd like Excel to insert new rows in order to paste...

Two columns on X-axis
How can I get a 90 degrees text alignment on x-axis from two columns. It's only column B taht rotate. Column A is still horisontel. The serie in column C gives a graf. Select whatever it is you want rotated 90=B0 Press Ctrl + 1 On the Alignment tab, under Orientation, set it to 90=B0 Click OK ...

Please Help: compare values in two columns present in seperate tab
Hi All, How can I compare values in two columns (columns are in different tables) ? I also want to display the values that dont match in a seperate table. For eg: If values in Column1 from Table1 does not match Values in Column1 from Table2 then display those values in a seperate table Is there a way to do this? THanks in advance Under query types, there is one for unmatched data and one for matched. -- Milton Purdy ACCESS State of Arkansas "sam" wrote: > Hi All, > > > How can I compare values in two columns (columns are in differe...

Comparing two columns of text data
I'm working with copies of someone else's SAP files. Machine operators enter "Notifications" into SAP, which schedulers turn into "Work Orders". After the job is complete, there is a row of data for each time anyone - operator, scheduler, mechanic, etc. - made a change to the record. Notifications have distinct numbers, as do work orders. So you may have: NOTIFICATION | WORK ORDER | ACTIVITY 0001 | 2985 | Notified pump out 0001 | 2985 | Scheduled work 0001 | 2985 | Calibrated pump 0001 | 2985 | Caliper post on first grid armature adjusted to thr...

Calc to show difference in numbers in two columns
Hi I have two columns of data. I would like a formula that will put a figure in column D that shows the difference between the figure in column B and Column C If column C is a higher figure, I would like the calculation in column D to be shown as Green. I'm sure this can be done with Conditional Formatting but it beats me. Any help will be greatly appreciated. Thanks in advance Malcolm For cell D2 try this formula: =C2-B2 then use Format --> Conditional Format and choose "Cell Value Is" along with "Greater Than" and type a 0 into the...

Is there any way of calculating a running total within a single cell in a column
Can anyone help with this query? e.g. I want to work out how many people are working at any time in a 24 hour period. The number of people rostered on differs each day of the week. Monday Time No. rostered on 6.00 2 7.00 2 8.00 2 9.00 2 10.00 (and so on) Can I add additional staff number in my 'no. rostered on column' by just entering the number into the cell. Say, if there were an extra 2 staff starting their 8 hour shift at 9. am. is there a way of just typing in '2' into that cell and getting '4'? What I do at the moment ...

how do i display the sort arrows in column headers in access 2007
I can't seem to find where I can turn on the sort arrows in table column headers. Any advice? Where are the column headers? In a table, query, form - data sheet - other? Some where else? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "KenBob2" <KenBob2@discussions.microsoft.com> wrote in message news:5280AF5F-F673-48D4-8C31-0051BF53A3F4@microsoft.com... >I can't seem to find where I can turn on the sort arrows in table column > headers. Any advice? I think it's: - office button (that upper left button) - access o...

Hidden column
A column is hidden. I can only see this column in the print preview mode. I tried to unhide it using the usual method of Format , Column and Unhide but its not working. Appreciate some expert advice. Can you select the columns to the left and right? If you can: highlight all three columns and then set the width to, say, 5. That may reveal it. I'm surprised you can see it in print preview if you can't see it on screen. The clever bods will be along soon to help you out, good luck. "showiesg" wrote: > A column is hidden. I can only see this colu...

VLOOKUP and sum
I have a list of project numbers, their monthly sales, and, 5 categories the projects fall into. For each month, I need to associate the project numbers with the five categories, and, sum the monthly sales for each category. (i.e. be able to paste the row of project numbers and associated sales into a spreadsheet and be able to produce the monthly sales of each of the 5 categories e.g. project # category <4000 S 4101 O 4102 C 4103 G 4104 I 4105 G 4106 C 4107 O 4108 O 41...

Hide column based on data value
I use auto-filter to switch a spreadsheet between a terse view and verbose view. Column B is auto-filtered, and it's either blank or non-blank. Call it a "tag column." Some column B cells have constants; others, formulas. It works fine. Can the same effect be achieved somehow to hide columns rather than rows? I'd like one row to be a "tag row." Then, I'd like to switch between terse view and verbose view by hiding columns based on what's in the tag row for each column. Can you suggest an easy way to do this without VBA? The "custom views" f...

Excel Function VLOOKUP
Hi, I'm having trouble looking up a table of "Names". The table deifene below is called ROL_IS and list hundred of other defined tables. Error where: =VLOOKUP(A33,VLOOKUP(CUR_MON,ROL_IS,3,FALSE),2,FALSE) but no errors where =VLOOKUP(A33,ACT_IS,2,FALSE) or =VLOOKUP(CUR_MON,ROL_IS,3,FALSE) ANS: ACT_IS The array function in VLOOKUP appears not to converting formula to NAME. If anyone knows or needs further details if would be appreciated. Thanks Elizabet -- Message posted from http://www.ExcelForum.com If I understand you correctly =VLOOKUP(A33,INDIRECT(VLOOKU...

Merging two select cases
I am trying to produce a single select statement which is true for two separate cases (so I can reuse it within an IN expression.. which means no IF statements). The answer is probably so simple I'll smack my head. Here is the setup: DROP TABLE #A; DROP TABLE #B; DROP TABLE #C; DROP TABLE #D; CREATE TABLE #A (ID int primary key clustered, PR_ID int null, [User_ID] int null) INSERT INTO #A (ID, PR_ID, [User_ID]) VALUES (99, NULL, 904551) CREATE TABLE #B (ID int primary key clustered, PR_ID int null, [User_ID] int null) INSERT INTO #B (ID, PR_ID, [User_ID]) VALUES (96, 57,...

Vlookup? #2
Can I use VLOOKUP to look up data in a data base and total multiple values associated with the lookup number in a range? Example: Database # $ 700 $325. 650 $225. 310 $110. 700 $500. 115 $475. 700 $125 Result #700= $950 --- Message posted from http://www.ExcelForum.com/ Vlookup is not the tool to use here. Try using SUMIF, it will fit the task nicely. (Also look at COUNTIF, DSUM for similar functions) --- Message posted from http://www.ExcelForum.com/ As Kieran says, try SUMIF instead of VLOOKUP Here's some steps to ease you-in on using ...

Search within a part of a column
Hello! I got a small question, which should not be any problem for a Excel specialist. I want to search through a part of a column (i.e. "E200:E250") for a specific text (i.e. "P"), and if in one cell of the specified range exactly this text is found, I want to set a seperate text (i.e. "B") in another cell (i.e. "E255). Has anyone of you some idea for me? I tried like =if(E200:E250="P"; "B"; ""), but is not working! Best regards, Peter -- OBIPEDA ------------------------------------------------------------------------ O...

is it possible to build a chart with clustered stacked columns ?
is it possible to build a chart with clustered stacked columns ? If you stagger your data, you can create side-by-side stacked columns. Bernard Liengme has an example and instructions on his site: http://www.stfx.ca/people/bliengme/ExcelTips/Columns.htm and Jon Peltier has links to other sites with information: http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Fabio Battagion wrote: > is it possible to build a chart with clustered stacked columns ? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Can columns be protecteded by an assigned user?
In an attempt to streamline 4 separate worksheets into one, is it possible to setup 4 columns in one Excel worksheet, then protect each column to allow one user to edit their assigned column by unlocking and relocking it? Or is there a better alternative, i.e., linking a main worksheet to each of the four that will show the edits automatically in my main worksheet? You can use the Allow Users to edit Range feature to set up a worksheet so that specific areas can be edited by specifc users. You protect the entire sheet and allow a user to edit a specific range without a pass...

Calculating a field based on what is in another column
Hi everyone, I have a 3 column spreadsheet. Amount Frequency Weekly$ (Calculated) 1200 F 600 i.e. divides it by 2 if F 100 W 5200 i.e. times it by weeks in year 5200 Y 100 ie divides it by weeks in year As you can see, I want the Weekly$ amt to be calculated based on whether the Frequency is W (Weekly), F (Fortnightly) or Y (Yearly). any ideas as to what to do ? cheers, Adam Assuming your data is in columns A and B, one way: =A1*INDEX({0.5,100,0.01},1,MATCH(B1,{...

How to reset column to standard width?
How can I remove the width setting from a column, so that its width will track future changes in the "standard width" for that spreadsheet? I'm using Excel 2000 9.0.6926 SP-3 under Windows 2000. - Rich As Bubba said, It depends on what your definition of "standard width" is activecolumn.columnwidth=8.43 -- Don Guillett SalesAid Software donaldb@281.com "Rich Pasco" <richp1234@hotmail.com> wrote in message news:%23$On9bIwDHA.2712@TK2MSFTNGP11.phx.gbl... > How can I remove the width setting from a column, so that its width > will track futu...

Query optimization advice for custom 12-column sales detail report
Hey all, I am designing a web-based 12-column sales report that will display 12 user specified periods across the top, items down the left, and the total sales for each period. The report will also suggest an order quantity based on sales. It is great for tracking how well an item was maintained (i.e. an item that was selling 100 / week suddenly drops to 0....oops!). The program will also allow users to set order reminders, and even place their order from the report. Anyway, it will be nice. The problem is that the report takes about 30 seconds per manufacturer for three months of data. Not...

Sharing common Outlook contacts across a workgroup
Is there a way to deny access to personal contact lists withing Outlook and have everyone working from a common contacts listing? "Gary Hill" <ghill@acmsi.com> schrieb im Newsbeitrag news:01b301c35220$b3439e10$a601280a@phx.gbl > Is there a way to deny access to personal contact lists > withing Outlook and have everyone working from a common > contacts listing? Hello Gary, have a look on this site for outlook sharing solutions: http://www.slipstick.com/outlook/share.htm -- Oliver Vukovics Share your Outlook pst file with several users: Public OutLook review on ZDN...