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

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