Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
combination INDEX//MATCH and IF-functions
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index match combination | Excel Worksheet Functions | |||
Index / match combination | Excel Discussion (Misc queries) | |||
Index and Match functions I think | New Users to Excel | |||
Combination...SumProduct, Index, Match? | Excel Worksheet Functions | |||
Row, Index, Match functions | Excel Discussion (Misc queries) |