ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also each prodct
has a L-number, based on the supplier. I use the INDEX/MACTH combination to
lookup information. In this case I have the L number, and want to find the
correct ID number per supplier. So I want the ID number from L-1234 IF (in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an IF-function?

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also each prodct
has a L-number, based on the supplier. I use the INDEX/MACTH combination to
lookup information. In this case I have the L number, and want to find the
correct ID number per supplier. So I want the ID number from L-1234 IF (in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an IF-function?

--

Dave Peterson

Thank you Dave. I tried your formula to practice, but unfortunately it doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for instance the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also each prodct
has a L-number, based on the supplier. I use the INDEX/MACTH combination to
lookup information. In this case I have the L number, and want to find the
correct ID number per supplier. So I want the ID number from L-1234 IF (in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an IF-function?

--

Dave Peterson

When you say you "tried your formula to practice", what formula did you use?
It's very difficult to point you in the right direction without knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but unfortunately it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for instance the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in
xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or 0 if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want to find
the
correct ID number per supplier. So I want the ID number from L-1234 IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an IF-function?

--

Dave Peterson

Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula did you use?
It's very difficult to point you in the right direction without knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but unfortunately it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for instance the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in
xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or 0 if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want to find
the
correct ID number per supplier. So I want the ID number from L-1234 IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an IF-function?

--

Dave Peterson

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mariekek5" wrote in message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula did you
use?
It's very difficult to point you in the right direction without knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but unfortunately it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for instance
the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from
a
third), you could use:

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

(all in 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 only use the whole column in
xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or 0 if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want to
find
the
correct ID number per supplier. So I want the ID number from L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson

Thank you for your reply.
I will stay with the same thread from now on, thanks for the advise.

I used your formula below, with semi-colon instead of asterisk..but still it
does not work. I now get #value

"RagDyeR" wrote:

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mariekek5" wrote in message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula did you
use?
It's very difficult to point you in the right direction without knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but unfortunately it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for instance
the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from
a
third), you could use:

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

(all in 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 only use the whole column in
xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or 0 if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want to
find
the
correct ID number per supplier. So I want the ID number from L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson

That's the same formula I used, and it returned 1237 for me. Are you sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred

"mariekek5" wrote in message
...
Thank you for your reply.
I will stay with the same thread from now on, thanks for the advise.

I used your formula below, with semi-colon instead of asterisk..but still
it
does not work. I now get #value

"RagDyeR" wrote:

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mariekek5" wrote in message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula did you
use?
It's very difficult to point you in the right direction without knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but unfortunately
it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for instance
the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value
from
a
third), you could use:

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

(all in 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 only use the whole column in
xl2007.

This returns the value in othersheet column C when column A and B
(of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or 0
if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except
in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes
trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also
each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want to
find
the
correct ID number per supplier. So I want the ID number from
L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson

It works indeed. Thanks to you all!




"Fred Smith" wrote:

That's the same formula I used, and it returned 1237 for me. Are you sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred

"mariekek5" wrote in message
...
Thank you for your reply.
I will stay with the same thread from now on, thanks for the advise.

I used your formula below, with semi-colon instead of asterisk..but still
it
does not work. I now get #value

"RagDyeR" wrote:

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mariekek5" wrote in message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula did you
use?
It's very difficult to point you in the right direction without knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but unfortunately
it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for instance
the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value
from
a
third), you could use:

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

(all in 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 only use the whole column in
xl2007.

This returns the value in othersheet column C when column A and B
(of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or 0
if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except
in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes
trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also
each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want to
find
the
correct ID number per supplier. So I want the ID number from
L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson

In order to save yourself a lot of time in the future, when you're asking
questions on this board, the safest thing to assume is that the answer you
receive is correct. If it's not working, it's probably something you did.
This will allow you do implement solutions within an hour of posting a
question, as opposed to the two days that it actually took.

Regards,
Fred.

"mariekek5" wrote in message
...
It works indeed. Thanks to you all!




"Fred Smith" wrote:

That's the same formula I used, and it returned 1237 for me. Are you sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred

"mariekek5" wrote in message
...
Thank you for your reply.
I will stay with the same thread from now on, thanks for the advise.

I used your formula below, with semi-colon instead of asterisk..but
still
it
does not work. I now get #value

"RagDyeR" wrote:

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mariekek5" wrote in message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula did
you
use?
It's very difficult to point you in the right direction without
knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but
unfortunately
it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for
instance
the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a
value
from
a
third), you could use:

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

(all in 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 only use the whole column
in
xl2007.

This returns the value in othersheet column C when column A and B
(of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or
0
if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns
(except
in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes
trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID
number";A1:C1;0))

In the following case each product has an unique ID-code. Also
each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want
to
find
the
correct ID number per supplier. So I want the ID number from
L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson

Dear Fred,

Thanks for your advise, you are totally right.

I have an additional question on the formula. Hope you can help me.

The fomula was:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

Is it possible to put an extra name in with 'OR'. So that Excel will look
for a number beloning to Homer OR Janssen OR Van Gogh in c1:c9?

In my document, there never will be a number belonging to Homer and/or
Janssen and/or Van Gogh. But these three suppliers do have other
concurrents... As its quite a large document I am working with, it would be
nice to copy the fomula to the whole column.

Thanks a lot in advance.

Marieke





"Fred Smith" wrote:

In order to save yourself a lot of time in the future, when you're asking
questions on this board, the safest thing to assume is that the answer you
receive is correct. If it's not working, it's probably something you did.
This will allow you do implement solutions within an hour of posting a
question, as opposed to the two days that it actually took.

Regards,
Fred.

"mariekek5" wrote in message
...
It works indeed. Thanks to you all!




"Fred Smith" wrote:

That's the same formula I used, and it returned 1237 for me. Are you sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred

"mariekek5" wrote in message
...
Thank you for your reply.
I will stay with the same thread from now on, thanks for the advise.

I used your formula below, with semi-colon instead of asterisk..but
still
it
does not work. I now get #value

"RagDyeR" wrote:

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mariekek5" wrote in message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula did
you
use?
It's very difficult to point you in the right direction without
knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but
unfortunately
it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for
instance
the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a
value
from
a
third), you could use:

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

(all in 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 only use the whole column
in
xl2007.

This returns the value in othersheet column C when column A and B
(of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or
0
if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns
(except
in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes
trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID
number";A1:C1;0))

In the following case each product has an unique ID-code. Also
each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want
to
find
the
correct ID number per supplier. So I want the ID number from
L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson

In addition. Is it also possible to ask for more 'AND's'?

Saying: I am looking for the x-code matching L-1234, if in column C it says
OR Homer, OR Van Gogh, OR Janssen, AND in colum E it says plastic.

"mariekek5" wrote:

Dear Fred,

Thanks for your advise, you are totally right.

I have an additional question on the formula. Hope you can help me.

The fomula was:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

Is it possible to put an extra name in with 'OR'. So that Excel will look
for a number beloning to Homer OR Janssen OR Van Gogh in c1:c9?

In my document, there never will be a number belonging to Homer and/or
Janssen and/or Van Gogh. But these three suppliers do have other
concurrents... As its quite a large document I am working with, it would be
nice to copy the fomula to the whole column.

Thanks a lot in advance.

Marieke





"Fred Smith" wrote:

In order to save yourself a lot of time in the future, when you're asking
questions on this board, the safest thing to assume is that the answer you
receive is correct. If it's not working, it's probably something you did.
This will allow you do implement solutions within an hour of posting a
question, as opposed to the two days that it actually took.

Regards,
Fred.

"mariekek5" wrote in message
...
It works indeed. Thanks to you all!




"Fred Smith" wrote:

That's the same formula I used, and it returned 1237 for me. Are you sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred

"mariekek5" wrote in message
...
Thank you for your reply.
I will stay with the same thread from now on, thanks for the advise.

I used your formula below, with semi-colon instead of asterisk..but
still
it
does not work. I now get #value

"RagDyeR" wrote:

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"mariekek5" wrote in message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula did
you
use?
It's very difficult to point you in the right direction without
knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in message
...
Thank you Dave. I tried your formula to practice, but
unfortunately
it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for
instance
the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a
value
from
a
third), you could use:

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

(all in 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 only use the whole column
in
xl2007.

This returns the value in othersheet column C when column A and B
(of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number (or
0
if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns
(except
in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes
trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID
number";A1:C1;0))

In the following case each product has an unique ID-code. Also
each
prodct
has a L-number, based on the supplier. I use the INDEX/MACTH
combination to
lookup information. In this case I have the L number, and want
to
find
the
correct ID number per supplier. So I want the ID number from
L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson

The ANDs are easy. You can add as many as you want, as in:
=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*("Plastic"=E1:E9");0) )

The ORs are more problematic. What cell do you want returned if, say, *both*
Homer and Van Gogh are in the range?

Regards,
Fred.

"mariekek5" wrote in message
...
In addition. Is it also possible to ask for more 'AND's'?

Saying: I am looking for the x-code matching L-1234, if in column C it
says
OR Homer, OR Van Gogh, OR Janssen, AND in colum E it says plastic.

"mariekek5" wrote:

Dear Fred,

Thanks for your advise, you are totally right.

I have an additional question on the formula. Hope you can help me.

The fomula was:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

Is it possible to put an extra name in with 'OR'. So that Excel will look
for a number beloning to Homer OR Janssen OR Van Gogh in c1:c9?

In my document, there never will be a number belonging to Homer and/or
Janssen and/or Van Gogh. But these three suppliers do have other
concurrents... As its quite a large document I am working with, it would
be
nice to copy the fomula to the whole column.

Thanks a lot in advance.

Marieke





"Fred Smith" wrote:

In order to save yourself a lot of time in the future, when you're
asking
questions on this board, the safest thing to assume is that the answer
you
receive is correct. If it's not working, it's probably something you
did.
This will allow you do implement solutions within an hour of posting a
question, as opposed to the two days that it actually took.

Regards,
Fred.

"mariekek5" wrote in message
...
It works indeed. Thanks to you all!




"Fred Smith" wrote:

That's the same formula I used, and it returned 1237 for me. Are you
sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred

"mariekek5" wrote in message
...
Thank you for your reply.
I will stay with the same thread from now on, thanks for the
advise.

I used your formula below, with semi-colon instead of
asterisk..but
still
it
does not work. I now get #value

"RagDyeR" wrote:

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===

"mariekek5" wrote in
message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell
C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula
did
you
use?
It's very difficult to point you in the right direction without
knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in
message
...
Thank you Dave. I tried your formula to practice, but
unfortunately
it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for
instance
the
"L-1234"), and then return the ID-number from a specific
supplier.

So if I want to return the ID number from "L-1234" supplied
by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a
value
from
a
third), you could use:

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

(all in 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 only use the whole
column
in
xl2007.

This returns the value in othersheet column C when column A
and B
(of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to
that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number
(or
0
if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns
(except
in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff
changes
trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail
he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID
number";A1:C1;0))

In the following case each product has an unique ID-code.
Also
each
prodct
has a L-number, based on the supplier. I use the
INDEX/MACTH
combination to
lookup information. In this case I have the L number, and
want
to
find
the
correct ID number per supplier. So I want the ID number
from
L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson

Great, thank you!

About the 'OR', in my document it will not happen that *both* Homer and Van
Gogh are in the range, because they are different names for the same
supplier.

Is it then possible to use the OR?

"Fred Smith" wrote:

The ANDs are easy. You can add as many as you want, as in:
=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*("Plastic"=E1:E9");0) )

The ORs are more problematic. What cell do you want returned if, say, *both*
Homer and Van Gogh are in the range?

Regards,
Fred.

"mariekek5" wrote in message
...
In addition. Is it also possible to ask for more 'AND's'?

Saying: I am looking for the x-code matching L-1234, if in column C it
says
OR Homer, OR Van Gogh, OR Janssen, AND in colum E it says plastic.

"mariekek5" wrote:

Dear Fred,

Thanks for your advise, you are totally right.

I have an additional question on the formula. Hope you can help me.

The fomula was:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

Is it possible to put an extra name in with 'OR'. So that Excel will look
for a number beloning to Homer OR Janssen OR Van Gogh in c1:c9?

In my document, there never will be a number belonging to Homer and/or
Janssen and/or Van Gogh. But these three suppliers do have other
concurrents... As its quite a large document I am working with, it would
be
nice to copy the fomula to the whole column.

Thanks a lot in advance.

Marieke





"Fred Smith" wrote:

In order to save yourself a lot of time in the future, when you're
asking
questions on this board, the safest thing to assume is that the answer
you
receive is correct. If it's not working, it's probably something you
did.
This will allow you do implement solutions within an hour of posting a
question, as opposed to the two days that it actually took.

Regards,
Fred.

"mariekek5" wrote in message
...
It works indeed. Thanks to you all!




"Fred Smith" wrote:

That's the same formula I used, and it returned 1237 for me. Are you
sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred

"mariekek5" wrote in message
...
Thank you for your reply.
I will stay with the same thread from now on, thanks for the
advise.

I used your formula below, with semi-colon instead of
asterisk..but
still
it
does not work. I now get #value

"RagDyeR" wrote:

That last asterisk should be a semi-colon:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may
benefit!
================================================== ===

"mariekek5" wrote in
message
...
Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

For some reason I now get the value "8769", which is from cell
C9.

I dont understand why...

"Fred Smith" wrote:

When you say you "tried your formula to practice", what formula
did
you
use?
It's very difficult to point you in the right direction without
knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred

"mariekek5" wrote in
message
...
Thank you Dave. I tried your formula to practice, but
unfortunately
it
doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for
instance
the
"L-1234"), and then return the ID-number from a specific
supplier.

So if I want to return the ID number from "L-1234" supplied
by
Holmer....I
would get number: 1237.

Can you help me with this specific case?

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a
value
from
a
third), you could use:

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

(all in 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 only use the whole
column
in
xl2007.

This returns the value in othersheet column C when column A
and B
(of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to
that
product
portion of the formula:

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

============
If there is only one match and you're bringing back a number
(or
0
if
there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns
(except
in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff
changes
trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail
he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mariekek5 wrote:

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID
number";A1:C1;0))

In the following case each product has an unique ID-code.
Also
each
prodct
has a L-number, based on the supplier. I use the
INDEX/MACTH
combination to
lookup information. In this case I have the L number, and
want
to
find
the
correct ID number per supplier. So I want the ID number
from
L-1234
IF
(in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an
IF-function?

--

Dave Peterson