"Positive" wrote in message
ups.com...
On Jun 4, 2:25 pm, "T. Valko" wrote:
"Positive" wrote in message

ups.com...
On Jun 4, 2:27 am, "T. Valko" wrote:



Have the file that contains this data open:

Jack 0 $35,000
Allen 2 $45,000
Jack 3 $46,000
Mike 4 $72,000

In a cell in the file where you want the formula start typing in the
formula:

=SUMPRODUCT(--(

Now, navigate to the open source file and select the sheet and range for
the
name. Excel will add the file name for you. It'll look something like
this:

=SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10

Now, start typing again and add the criteria for the name:

="Jack"),--(

Now, point to the range for the ID# then start typing again and add the
criteria for the ID#:

=3),

Now, point to the range for the salary then finish the formula by typing
a
closing ).

When you're done the formula should look like this:

=SUMPRODUCT(--([file.xls]Sheet1!$A$1:$A$10="Jack"),--([file.xls]Sheet1!$B$1**:$B$10=3),[file.xls]Sheet1!$C$1:$C$10)

Pointing to the ranges in the open source file is *much* easier than
typing
all that stuff in.

Biff

"Positive" wrote in message

roups.com...

I need to look up name and then id then return the salary to a
different workbook.
e.g
Jack 0 $35,000
Allen 2 $45,000
Jack 3 $46,000
Mike 4 $72,000

I want to look up Jack who has id #3 and put his salary to a DIFFERENT
WORKBOOK.

Please help- Hide quoted text -

- Show quoted text -

****************************
Biff, I cannot thank you enough. No explanation could be more explicit
than this.
I have another question. I have a spreadsheet e.g

Jack 20 (utility)
600 (rent)
50 (phone)

Jill 50(utility)
1000(rent)
70(phone)
Tom 40 (utility)
700 (rent)
45 (phone)

This info needs to be updated monthly to a DIFFERENT WORKBOOK. I know
how to use VLOOKUP for 1 item but for 3 items per person, I don't know
how. Please help

Positive

**************************

You can't use VLOOKUP for this.

Assume the names are in column A, amounts in column B.

Try this:

Cell E2 is the lookup value = some name

Enter this formula F2 and copy down a total of 3 cells:

=INDEX(B$2:B$10,MATCH(E$2,A$2:A$10,0)+ROWS($1:1)-1)

Biff- Hide quoted text -

- Show quoted text -
*************************
Biff, but if I want to know how much rent that Jack pays and that
rent will be put in a DIFFERENT WORKBOOK, do I have to add
file.xls,sheet... with the formula. If I have to how. I am so confused
when it comes to complicated braces, brackets... parentheses...

Many thanks
*************************

Anytime you reference another file you have to include the path.

If you want to know the specific category for a particular person and the
categories follow the pattern as posted in your sample: 1. utility, 2. rent,
3. phone

Assume the source file is named file.xls

In the file where you want this info:

A2 = Jack

Formula to lookup Jack's rent:

=INDEX('[file.xls]Sheet1'!$B$2:$B$11,MATCH(A2,'[file.xls]Sheet1'!$A$2:$A$11,0)+1)

What you have to do is adjust the category offset from the persons name. For
example, the rent category is listed 2nd but it is offset from the persons
name by 1 row so in the formula that's what the +1 means. If you want the
persons utility that category is on the same row as the persons name so the
offset is 0. In that case the formula would use +0. If you want the persons
phone category that offset would be +2.

Biff