Look up 2 values, return result in a different workbook

[Positive]

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

 

[T. Valko]

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]

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

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 u enough. You are just great! No explanation
could be any exlicit than this.

 

[Positive]

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

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

 

[T. Valko]

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

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 u enough. You are just great! No explanation
could be any exlicit than this.

You're welcome. Thanks for the feedback!

Biff

 

[T. Valko]

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

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

 

[Positive]

Have the file that contains this data open:

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

news:[email protected]...

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

 

[T. Valko]

Have the file that contains this data open:

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

news:[email protected]...

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

 

[Positive]

"Positive" <[email protected]> wrote in message

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

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


You can't use VLOOKUP for this.

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

Try this:

Cell E2 is thelookupvalue = some name

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

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 tolookupJack'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- Hide quoted text -

- Show quoted text -

Biff, again THANKS A BUNCH. It works wonder and you have indirectlty
helped me to consolidate data and solve a lot of problems in report
automation at work.

 

[T. Valko]

"Positive" <[email protected]> wrote in message

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

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


You can't use VLOOKUP for this.

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

Try this:

Cell E2 is thelookupvalue = some name

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

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 tolookupJack'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- Hide quoted text -

- Show quoted text -

**********
Biff, again THANKS A BUNCH. It works wonder and you have indirectlty
helped me to consolidate data and solve a lot of problems in report
automation at work.
**********

You're welcome. Thanks for the feedback!

Biff

 

[Positive]

"Positive" <[email protected]> wrote in message

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

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 thelookupvalue = 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 tolookupJack'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- Hide quoted text -

- Show quoted text -

**********
Biff, again THANKS A BUNCH. It works wonder and you have indirectlty
helped me to consolidate data and solve a lot of problems in report
automation at work.
**********

You're welcome. Thanks for the feedback!

Biff- Hide quoted text -

- Show quoted text -

I have another situation e.g.

Jack 100 124
Jill 231 235
Tom 0 200
Henry 0 321

I need to look up the value in column B and put it in a DIFFERENT
WORBOOK, if column B shows 0 value,then it needs to take value in
column C.
E.g For Tom and Henry i will need to put down 200 and 321 in A
DIFFERENT WORKBOOK.
If it is in the same WORBOOK,then I would know how to use IF function,
but in this case I don'r know how to combine VLOOKUP and IF funtions.
Please help
Thanks -Positive

 

[T. Valko]

"Positive" <[email protected]> wrote in message

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

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 thelookupvalue = 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 tolookupJack'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- Hide quoted text -

- Show quoted text -

**********
Biff, again THANKS A BUNCH. It works wonder and you have indirectlty
helped me to consolidate data and solve a lot of problems in report
automation at work.
**********

You're welcome. Thanks for the feedback!

Biff- Hide quoted text -

- Show quoted text -

**********
I have another situation e.g.

Jack 100 124
Jill 231 235
Tom 0 200
Henry 0 321

I need to look up the value in column B and put it in a DIFFERENT
WORBOOK, if column B shows 0 value,then it needs to take value in
column C.
E.g For Tom and Henry i will need to put down 200 and 321 in A
DIFFERENT WORKBOOK.
If it is in the same WORBOOK,then I would know how to use IF function,
but in this case I don'r know how to combine VLOOKUP and IF funtions.
Please help
Thanks -Positive
**********

Try something like this:

=VLOOKUP(A1,[file.xls]Sheet1!$A$1:$C$4,IF(VLOOKUP(A1,[file.xls]Sheet1!$A$1:$C$4,2,0)=0,3,2),0)

Biff

 

[Positive]

When I use SUMPRODUCT formula, especially to look up data from
different workbooks and return to a different workbook, I tend to have
problem later on when I work on it or save it to a different file. All
my other formulas e.g IF, VLOOK never been messed up when I work on
the file or save it to a different file.

Is there anyway I could protect this SUMPRODUCT formula or any reason
why it get messed up so easily?
Thanks

Positive