#### VLookup 05-27-10

```I have a workbook with two worksheets.  On the first - it has the following
columns:

Inv #     Employee     Fees Paid

on the second worksheet i have the following:

Inv #     Employee     Fees Billed     Hrs Billed

I want to be able to pull the information from the 2nd worksheet onto the
first one.  I know how to do vertical lookups but in this case, I need two.
I need the system to first look for the invoice # and then look for the
employee.  Once it sees those two matches, i want it to populate the fees
billed and hours billed.

Is this possible?

Thanks!
```
 0
Utf
5/27/2010 9:27:29 PM
excel.worksheet.functions 4936 articles. 2 followers.

5 Replies
1000 Views

Similar Articles

[PageSpeed] 44

```I almost have it but am getting an error.  If i am interpreting the formula
correctly, it says to give me the value in Sheet2 column c when both column a
and column b in both worksheets are the same.  can you tell me what the last
ROW(\$A\$1:\$A\$10) is telling it?

"Jacob Skaria" wrote:

> Try the below formula in sheet1 say in (cell D1) and copy to the right to
> retrieve  Fees Billed  &   Hrs Billed. Format the formula cell to display as
> hours...
>
> =INDEX(Sheet2!C\$1:C\$10,SUMPRODUCT((Sheet2!\$A\$1:\$A\$10=\$A1)*
> (Sheet2!\$B\$1:\$B\$10=\$B1),ROW(\$A\$1:\$A\$10)))
>
> --
> Jacob (MVP - Excel)
>
>
> "Mary Lou" wrote:
>
> > I have a workbook with two worksheets.  On the first - it has the following
> > columns:
> >
> > Inv #     Employee     Fees Paid
> >
> > on the second worksheet i have the following:
> >
> > Inv #     Employee     Fees Billed     Hrs Billed
> >
> > I want to be able to pull the information from the 2nd worksheet onto the
> > first one.  I know how to do vertical lookups but in this case, I need two.
> > I need the system to first look for the invoice # and then look for the
> > employee.  Once it sees those two matches, i want it to populate the fees
> > billed and hours billed.
> >
> > Is this possible?
> >
> > Thanks!
```
 0
Utf
5/27/2010 8:22:59 PM
```nevermind my last question.  i think i was looking at a different response.
going back to the drawing board.

"Jacob Skaria" wrote:

> Try the below formula in sheet1 say in (cell D1) and copy to the right to
> retrieve  Fees Billed  &   Hrs Billed. Format the formula cell to display as
> hours...
>
> =INDEX(Sheet2!C\$1:C\$10,SUMPRODUCT((Sheet2!\$A\$1:\$A\$10=\$A1)*
> (Sheet2!\$B\$1:\$B\$10=\$B1),ROW(\$A\$1:\$A\$10)))
>
> --
> Jacob (MVP - Excel)
>
>
> "Mary Lou" wrote:
>
> > I have a workbook with two worksheets.  On the first - it has the following
> > columns:
> >
> > Inv #     Employee     Fees Paid
> >
> > on the second worksheet i have the following:
> >
> > Inv #     Employee     Fees Billed     Hrs Billed
> >
> > I want to be able to pull the information from the 2nd worksheet onto the
> > first one.  I know how to do vertical lookups but in this case, I need two.
> > I need the system to first look for the invoice # and then look for the
> > employee.  Once it sees those two matches, i want it to populate the fees
> > billed and hours billed.
> >
> > Is this possible?
> >
> > Thanks!
```
 0
Utf
5/27/2010 8:33:22 PM
```Try the below formula in sheet1 say in (cell D1) and copy to the right to
retrieve  Fees Billed  &   Hrs Billed. Format the formula cell to display as
hours...

=INDEX(Sheet2!C\$1:C\$10,SUMPRODUCT((Sheet2!\$A\$1:\$A\$10=\$A1)*
(Sheet2!\$B\$1:\$B\$10=\$B1),ROW(\$A\$1:\$A\$10)))

--
Jacob (MVP - Excel)

"Mary Lou" wrote:

> I have a workbook with two worksheets.  On the first - it has the following
> columns:
>
> Inv #     Employee     Fees Paid
>
> on the second worksheet i have the following:
>
> Inv #     Employee     Fees Billed     Hrs Billed
>
> I want to be able to pull the information from the 2nd worksheet onto the
> first one.  I know how to do vertical lookups but in this case, I need two.
> I need the system to first look for the invoice # and then look for the
> employee.  Once it sees those two matches, i want it to populate the fees
> billed and hours billed.
>
> Is this possible?
>
> Thanks!
```
 0
Utf
5/27/2010 9:12:32 PM
```Your situation sounds similar to mine.  See the question entitled "Two Level
Search" - about 7 "posts" below yours.  I was able to use the advice prvided
(by Skaria), and it works very well.

"Mary Lou" wrote:

> I have a workbook with two worksheets.  On the first - it has the following
> columns:
>
> Inv #     Employee     Fees Paid
>
> on the second worksheet i have the following:
>
> Inv #     Employee     Fees Billed     Hrs Billed
>
> I want to be able to pull the information from the 2nd worksheet onto the
> first one.  I know how to do vertical lookups but in this case, I need two.
> I need the system to first look for the invoice # and then look for the
> employee.  Once it sees those two matches, i want it to populate the fees
> billed and hours billed.
>
> Is this possible?
>
> Thanks!
```
 0
Utf
5/27/2010 9:20:46 PM
```Look in the help index for MATCH and INDEX

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Mary Lou" <MaryLou@discussions.microsoft.com> wrote in message
news:EEEC1829-0B9B-4B84-B1F9-2C0742CA4CBE@microsoft.com...
>I have a workbook with two worksheets.  On the first - it has the following
> columns:
>
> Inv #     Employee     Fees Paid
>
> on the second worksheet i have the following:
>
> Inv #     Employee     Fees Billed     Hrs Billed
>
> I want to be able to pull the information from the 2nd worksheet onto the
> first one.  I know how to do vertical lookups but in this case, I need
> two.
> I need the system to first look for the invoice # and then look for the
> employee.  Once it sees those two matches, i want it to populate the fees
> billed and hours billed.
>
> Is this possible?
>
> Thanks!

```
 0
Don
5/27/2010 11:20:29 PM

Similar Artilces:

Interesting challenge to highlight instances of >10 consecutive days scheduled
This is a multi-part message in MIME format. ------=_NextPart_000_0034_01C3E99D.D9DCF660 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In column A (A12:A377) I have this year's dates by day. In a cell in = another column, let's say cell D1, I want to put in a calculation that = will present the word "OVER-SCHEDULED" when there is ever a time when = someone has been scheduled in to work more that 10 consecutive days in a = row. The calculation must look for the following schedule codes to = determine a work shift: D,E,N...

How to stop a vlookup function in vba
Hello! I have this macro that inserts in a number of cells the function vlookup. The problem I have is that this macro is too slow. I would like to add an instruction to the macro to stop the vlookup function and once the macro ends activate again this function. I=B4ve tried with Application.calculation=3Dxlmanual but it=B4s slow. Thanks As ALWAYS!!, post your macro for comments -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "1234" <altachicasaudaces@gmail.com> wrote in message news:91b28358-2c6e-4f25-99f0-aebaeee99a1a@s2...

gp 10 sp2
we are using gp 10 ,we have 3 companies at gp ,2 of them have analytical accounting activated,the third company doesnt have aa activated,installed service pack,while running utilities,it worked ok for 2 companies containing aa,but the third company gave an error cant create an index on aag000904 because this table doesnt exist in databse,and I cant log in to this company as it gives an error that I have to open utilities to finish setup for that company There is a possibility that you are running the Utilities from a machine which contains AA dictionary and it is also upgraded to SP2...

Import Customer Records
I am trying to import customer records and I am running into the following error: DOC 1 ERROR: Are you sure you want to delete this customer record? Ln 810 Col 2, Update module, Last CBOM field read was root.'Customer ID', Last window field visited was 'Clear Button' of window 'RM_Customer_Maintenance' of form 'RM_Customer_Maintenance' Any ideas? Thanks! Are you on the latest integration manager service pack? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at h...

Data Cleansing 06-09-05
Is there a way to run a report on all the data entry fields in CIS so that I can submit to the Operations Director. He is tasked with creating a style guide for data entry and has asked that I run a report for him on those fields. If not, any suggestions on the easiest way to handle this. Thank you. -Bobby Please explain what you mean by CIS "Bobby" <Bobby@discussions.microsoft.com> wrote in message news:DE071EDC-CCE7-45FA-AA12-F69EC325CE24@microsoft.com... > Is there a way to run a report on all the data entry fields in CIS so that > I > can submit to the ...

Outlook 2003 03-24-10
When a meeting is schedule and the email is sent, I make a time change, update the meeting but the attendees do not get the notification unless I resend the meeting. "Barbara" <Barbara@discussions.microsoft.com> wrote in message news:F8AEFBAC-A2C0-477A-B048-49DDA0FFF50F@microsoft.com... > When a meeting is schedule and the email is sent, I make a time change, > update the meeting but the attendees do not get the notification unless I > resend the meeting. You must send the update if you expect your attendees to know about it. -- Brian Tillman [MVP...

Publisher 2000 #27
We are about to take a course in Publisher 2000. We have '95, '97, 2002 and 2003. While we don't have 2000 and that is the version being taught, can you suggest which version would be the closest to 2000 without having to purchase the version 2000. Would appreciate your answer. A small child turns to Ed, and exclaims: "Look! Look! A post from Office Center of Gurnee <oficecenterofgurnee@comcast.net>!"... > We are about to take a course in Publisher 2000. We > have '95, '97, 2002 and 2003. While we don't have 2000 > and that is the vers...

New View List gp-10.0
Hello - the view list in 10.0 are good. If I try to search on filed not shown in the columns , I don't get any data. How do you add columns, such as, customer ID in the sales view list? Can you modify the reports to include totals (they are not report writer reports)? Thanks -- Crystal Roach Ok - I finally figured it out - not only do you have to have security set to customize the list view, but you have to save the list before you can customize. However, I still don't see a way to add a total to the report. Any assistance is appreiated. -- Crystal Roach "Crystal Cl...

This is very odd. The first time through is fine. If I leave Word running then the next run is good too. If I close Word then the next one fails on calling the dialog for new files. 462 Error. If I open Word first manually I still get the error. Any ideas? Code below... Sometimes it will run again - maybe after a compile or something. Am trying to find out what will trigger it. Thanks Dim objWordApp As Object Dim rsLetterData As DAO.Recordset Dim qdfLetterData As DAO.QueryDef Dim strTemplate As String Dim i As Integer Dim intLength As Integer Dim WaitFor As Single Dim S...

Line Chart 03-22-10
I am trying to create a chart that will allow me to have multiple years on the same chart so the difference can be seen from year to year. Second problem is there a limit to what can be entered into the axis. I'm not sure if this is clear or not. Appreciate any assistance. EXCEL 2007 Please check out:- http://www.pierrefondes.com/ - item number 53. Open the Workbook and go to the Chart Worksheet. 1. Change data range A4 to A8 to, for example:- 1990 1991 1992 1993 1994 These dates will then appear in the Horizontal Axis of the chart. This gives y...

adding items to the action pane in GP 10
We need to offer the capability to add additional windows to the "Go To" button in the Action Panes, beyond the (very) limited options currently available. For example, in the Accounts view of the Financial Center, there is no way to Add to the "Go To" a link to the "Actual vs. Budget Inquiry Window". Lacking this feature requires the user to either return to the Financial Center page, or to use the drop -down menus (Inquiry >> Financial >> Actual vs Budget). Either approach requires the user to re-enter the account number, essentially starti...

email 11-05-07
DQoiY2FybG9tYXJ6YW5vIiA8Y2FybG9tYXJ6YW5vQGVtYmFycW1haWwuY29tPiB3cm90ZSBpbiBt ZXNzYWdlIG5ld3M6TzlSMW1YMUhJSEEuNTE2MEBUSzJNU0ZUTkdQMDUucGh4LmdibC4uLg0KPiAN Cj4gDQo+ ...

Help needed with Vlookup code
I have noticed other vlookup codes in excel and after looking at them have started to try and use them myself. 2 questions. 1) The codes i saw had a lot of \$ signs in them. I dont use them when i use vlookup and it seems to work fine. Is there a reason \$ signs are added? 2) I have a vlookup code "=VLOOKUP(A9,Database!A1:F8000,2,FALSE)" and so on for a few lines and when it finds a blank box it puts 0 in there to represent that. Is there a way i can have it so that instead of putting a zero in it just leaves the box blank? -- Help Needed -------------------------------------...

Error Messages 03-17-05
I have started getting a lot of error messages that simply say, "error, please see system administrator" when I am trying to close opportunities. Well, I am the system administrator and not knowing any specifics about an error leaves me nowhere to start looking to fix it. Does anyone have any ideas? Take a look at the event log on the server, it usually provides more detail on the error message. Be sure that the following key is set in the web.config file. <add key="LogErrors" value="On"/> "Dave" wrote: > I have started getting a lo...

indirect function 02-04-10
I'm not familiar with why / when the INDIRECT function is used. I've inherited a spreadsheet, it has 10+ spreadsheets, and throughout them there are several different scenarios of the INDIRECT functionm,this is the simple one =INDIRECT("c"&ROW()). Would someone please explain in laymans terms the purpose of using this function. Much appreciated. C1: cat C2: dog C3: chicken C4: cow your formula in D2 D2: =INDIRECT("C",ROW()) equivalent with =INDIRECT("C2") It returns "dog" "WINDMILL" wrote: > I&...

Input from remote 10-key and laptop numbers very slow
The last few days I've experience a delay when inputting numbers into Excel 2000 worksheets. When I type in the numbers, they slowly enter themselves, one at a time pausing between each number. This is occurring both with the remote 10-key for the laptop and the number keys on the laptop. This is occuring in all files, no matter how big or small. I wouldn't say I have any overly complicated workbooks, mostly basic functions. I have had much bigger and more complicated before without a problem. When using the 10-key or the number keys anywhere else, Word, internet, e...

Office:mac 2004 and Tiger (OS X 10.4)
X-posted to: microsoft.public.mac.office and microsoft.public.mac.office.word. Followup-to: microsoft.public.mac.office.word Hello. Some years ago - although after the release of Tiger, OS X v10.4 in April 2005 - I read in one of the microsoft.public.mac newsgroups a posting from an MVP who was still using Office:mac 2004 with Panther, in this case OS X v10.3.9, and that this was because Office:mac 2004 was developed on Jaguar, OS X v10.2 and only works 100 pct with OS X up to v10.3.9. I have been running Office:mac 2004 on a Powerbook FW800 with Panther, OS X v10.3.9 and now want to...

Workflow Manager 10-16-03
Hi all, I have the same problem as discussed here. But no solution suggested here was successfully :-( I've played with the rights of the workflow service, and so on. Alwyas the same error: "You do not have sufficient rights ...". Any other ideas? Ralf. there is a fix for this - https://www.greatplains.com/customersource/support/downloads/hotfixes/tk29313hotfix.htm "Ralf Eisele" <ralf@extension.net> wrote in message news:uLrQ%2349kDHA.2216@TK2MSFTNGP12.phx.gbl... > Hi all, > > I have the same problem as discussed here. But no solution suggested he...

add values using vlookup over multi sheets
I have a workbook which contains about 20 worksheets. In sheet1 I have a list of numbers which could be also on the other 19 sheets. What I need to do is look at each of the other 19 sheets to see if they have the number which is on sheet1 and if so I need to get the numerical data in the 5th column to be returned and added together. ie sheet1 a1 = 2001 sheet4 a5 = 2001 e5 = 2 sheet6 a21 = 2001 e21 = 5 therefore sheet1 b1 = e5 + e21 = 7 Hopefully someone could help me. Thanks Noemi Try... =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("2:20"))&&q...

Money 05 just sucks---so unstable
EVERY FREAKIN time I click on UPDATE Microsoft Money 2005 closes itself. If I reboot, sometimes it works, sometimes it doesn't! I mean, what a piece of crap software! Besides Windows 98, I think this is the crappiest piece of software Microsoft has released to date. Alright folks... for problems with update. 1. Delete all of your Internet Explorer temporary files 2. Delete all of your %system%\temp files As soon as I did that, MS Money would run the update. Please add this to the FAQ! >-----Original Message----- > >EVERY FREAKIN time I click on UPDATE Microsoft Money ...

Access 2007 Runtime 10-30-07
1. Can I distribute Accees 2003 applications using Access 2007 runtime? 2. Do I also need the runtime extensions? 3. Will installing the Access 2007 runtime and extensions affect my current Access 2003 (full version) software, already installed? ...

help 04-20-10
since yesterday when ever i try and open an icon on my desk top this message comes up windows cannot access the specified file, pathway you may not have permission to access it? what does this mean and how do i fix it???? Also about a week ago when i was on the internet these system messages kept coming up saying my computer was in trouble and peple were trying to access my personal information and i needed to upgrage my spyware. i couldnt go into any programes except the windows security site and then i had to buy the 6 month protection to access anything on my computer. now ...

Match 10 xls files
Hello, I created 10 xls files with exact the same fields. Field B2 is filled in xls sheet 2 Field B3 in sfilled in on xls sheet 3 B4 on sheet 4 .... I want to match all the xls files to 1 so that in 1 file everthing is filled in. (The 10 xls files are distributed to 10 different persons for the count of our stock in our company and then we will import the xls inot our database) How can I do this ? Thanks in advance ! Tom ...

Multiple version instances in GP 9.0 and 10.0
I am trying to run multiple instances on one machine for GP 9.0 and GP 10.0 using a second instance of SQL Server 2005, although I get a Dexterity Runtime error. Is it necessary to have this second instance of SQL? Any clues? Thanks in advance. Each instance must have it's own DYNAMICS database. Also, make sure that you have two seperate DSN's, one that points to version 9, one that points to version 10. When you log into GP, make sure you use the correct DSN for that version. Hope that helps, Jim "Kelly" wrote: > I am trying to run multiple instances on one ...

Query 04-09-10
Good day I have a query that reads from a single table with two criteria. I would like to extract from 1 or more districts at the same time choosing one or more programmes. If possible I would like the info on what to put in the fields of the query on the criteria line, as I am not familiar with SQL. Thanks On Fri, 9 Apr 2010 10:53:01 -0700, Christina <Christina@discussions.microsoft.com> wrote: >Good day >I have a query that reads from a single table with two criteria. I would >like to extract from 1 or more districts at the same time choosing one or ...