Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
I have a master price list were I have in column A the country name, and in
column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
1 way
E2=SUMPRODUCT((A2:A100="usa")*(B2:B100=1)*(C2:C100 )) another: put country in D2 and model number in D3 E2=SUMPRODUCT((A2:A100=D2)*(B2:B100=D3)*(C2:C100)) "Farah" skrev: I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
=INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
Bob,
I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
Jim,
This is matching two values against two ranges. So what it does is compare one value against one range A1:A100="country" which returns an array of TRUE/FALSE values aka the SUMPRODUCT tests that we know and love. Similarly B1:B100="machine" returns another array of TRUE/FALSE. By using the * operator, we coerce them to a single array of 1/0 values. The MATCH statement is then used to find the first 1 within that array, and that index number is passed to the INDEX function to find the matching item in the third range. This technique does assume only a singleton match, it cannot find multiples, it will find the first if multiples exist. But that is no different to VLOOKUP. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
ah so;
Thanks Bob; So I am looking for (the first) 1 (which is in reality a TRUE); Great - E-X-P-A-N-D-I-N-G- T-H-E M-I-N-D ,,,,, "Bob Phillips" wrote: Jim, This is matching two values against two ranges. So what it does is compare one value against one range A1:A100="country" which returns an array of TRUE/FALSE values aka the SUMPRODUCT tests that we know and love. Similarly B1:B100="machine" returns another array of TRUE/FALSE. By using the * operator, we coerce them to a single array of 1/0 values. The MATCH statement is then used to find the first 1 within that array, and that index number is passed to the INDEX function to find the matching item in the third range. This technique does assume only a singleton match, it cannot find multiples, it will find the first if multiples exist. But that is no different to VLOOKUP. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
Probably it's easier to understand a bit faster version...
=INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
.... which in reality a pair of TRUEs, which equates to the first row that
matches both conditions. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... ah so; Thanks Bob; So I am looking for (the first) 1 (which is in reality a TRUE); Great - E-X-P-A-N-D-I-N-G- T-H-E M-I-N-D ,,,,, "Bob Phillips" wrote: Jim, This is matching two values against two ranges. So what it does is compare one value against one range A1:A100="country" which returns an array of TRUE/FALSE values aka the SUMPRODUCT tests that we know and love. Similarly B1:B100="machine" returns another array of TRUE/FALSE. By using the * operator, we coerce them to a single array of 1/0 values. The MATCH statement is then used to find the first 1 within that array, and that index number is passed to the INDEX function to find the matching item in the third range. This technique does assume only a singleton match, it cannot find multiples, it will find the first if multiples exist. But that is no different to VLOOKUP. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
Guys,
It is not working, see I can pass you the files if possible just tell me how as I am new to this whole thing. The point is that I have many machines for one country and I have many countries, to give you an idea, I have seven coutries and for each country and I have more than three hundred machines prices. The system given me a dupm showing in one column the country name and in another column the machine name and then the price. I need a function that checks the country in one column and in the other columnn it checks the machine, if both are OK then it should give me the price which is in another column. Regards, Farah "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
Which is exactly what we gave you. Did you try it? what happened when you
did? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... Guys, It is not working, see I can pass you the files if possible just tell me how as I am new to this whole thing. The point is that I have many machines for one country and I have many countries, to give you an idea, I have seven coutries and for each country and I have more than three hundred machines prices. The system given me a dupm showing in one column the country name and in another column the machine name and then the price. I need a function that checks the country in one column and in the other columnn it checks the machine, if both are OK then it should give me the price which is in another column. Regards, Farah "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
It gave me an error which is #NUM though I checked it many times. The point
is that in the countries list, the country is repeated many times, and in the machine model column, the machine is repeated many times, you need to create a lookup function which will search for the machine and then when it founds the machine search in another column for the country and then when both the machine and the country matches your search criteria you need to get the price from another column, which means basically that you have a two columns lookup procedure. Regards, "Bob Phillips" wrote: Which is exactly what we gave you. Did you try it? what happened when you did? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... Guys, It is not working, see I can pass you the files if possible just tell me how as I am new to this whole thing. The point is that I have many machines for one country and I have many countries, to give you an idea, I have seven coutries and for each country and I have more than three hundred machines prices. The system given me a dupm showing in one column the country name and in another column the machine name and then the price. I need a function that checks the country in one column and in the other columnn it checks the machine, if both are OK then it should give me the price which is in another column. Regards, Farah "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
I completely understand how your data is laid out and the formula I gave you
should do what you want. Show me the exact formula that you used. an example of the data would help too. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... It gave me an error which is #NUM though I checked it many times. The point is that in the countries list, the country is repeated many times, and in the machine model column, the machine is repeated many times, you need to create a lookup function which will search for the machine and then when it founds the machine search in another column for the country and then when both the machine and the country matches your search criteria you need to get the price from another column, which means basically that you have a two columns lookup procedure. Regards, "Bob Phillips" wrote: Which is exactly what we gave you. Did you try it? what happened when you did? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... Guys, It is not working, see I can pass you the files if possible just tell me how as I am new to this whole thing. The point is that I have many machines for one country and I have many countries, to give you an idea, I have seven coutries and for each country and I have more than three hundred machines prices. The system given me a dupm showing in one column the country name and in another column the machine name and then the price. I need a function that checks the country in one column and in the other columnn it checks the machine, if both are OK then it should give me the price which is in another column. Regards, Farah "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
I attached below the data, I am sorry it is not tidy enough but the first
column is the machine model, then you can see the country and then the price. D28886-QS Dewalt Die Grinder. 710W variable speed 13456 IRAQ 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13457 JORDAN 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13458 LEBANON 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13459 SYRIA 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13460 EGYPT 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13461 LIBYA 126 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13462 MOROCCO 116 EUR D28886-QS Dewalt Die Grinder. 710W variable speed 13463 TUNISIA 134 EUR D28886-QS Dewalt Die Grinder. 710W variable speed 13464 ALGERIA 140.36 USD D51238K-QS USE XJ Version 13457 JORDAN 122.9 USD D51238K-QS USE XJ Version 13458 LEBANON 122.9 USD D51238K-QS USE XJ Version 13459 SYRIA 117 USD D51238K-QS USE XJ Version 13460 EGYPT 122.9 USD D51238K-QS USE XJ Version 13461 LIBYA 125 USD D51238K-QS USE XJ Version 13462 MOROCCO 110 EUR D51238K-QS USE XJ Version 13463 TUNISIA 120 EUR D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13456 IRAQ 132.3 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13457 JORDAN 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13458 LEBANON 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13459 SYRIA 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13460 EGYPT 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13461 LIBYA 131.3 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13462 MOROCCO 115.5 EUR D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13464 ALGERIA 139.76 USD D51256K-QS USE XJ VERSION 13456 IRAQ 183.8 USD "Bob Phillips" wrote: I completely understand how your data is laid out and the formula I gave you should do what you want. Show me the exact formula that you used. an example of the data would help too. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... It gave me an error which is #NUM though I checked it many times. The point is that in the countries list, the country is repeated many times, and in the machine model column, the machine is repeated many times, you need to create a lookup function which will search for the machine and then when it founds the machine search in another column for the country and then when both the machine and the country matches your search criteria you need to get the price from another column, which means basically that you have a two columns lookup procedure. Regards, "Bob Phillips" wrote: Which is exactly what we gave you. Did you try it? what happened when you did? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... Guys, It is not working, see I can pass you the files if possible just tell me how as I am new to this whole thing. The point is that I have many machines for one country and I have many countries, to give you an idea, I have seven coutries and for each country and I have more than three hundred machines prices. The system given me a dupm showing in one column the country name and in another column the machine name and then the price. I need a function that checks the country in one column and in the other columnn it checks the machine, if both are OK then it should give me the price which is in another column. Regards, Farah "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
This works fine for me
=INDEX(C1:C100,MATCH(1,(B1:B100="LEBANON")*(A1:A10 0="D51238K-QS USE XJ Version 13458"),0)) although I do notice that all the machines are unique -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I attached below the data, I am sorry it is not tidy enough but the first column is the machine model, then you can see the country and then the price. D28886-QS Dewalt Die Grinder. 710W variable speed 13456 IRAQ 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13457 JORDAN 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13458 LEBANON 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13459 SYRIA 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13460 EGYPT 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13461 LIBYA 126 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13462 MOROCCO 116 EUR D28886-QS Dewalt Die Grinder. 710W variable speed 13463 TUNISIA 134 EUR D28886-QS Dewalt Die Grinder. 710W variable speed 13464 ALGERIA 140.36 USD D51238K-QS USE XJ Version 13457 JORDAN 122.9 USD D51238K-QS USE XJ Version 13458 LEBANON 122.9 USD D51238K-QS USE XJ Version 13459 SYRIA 117 USD D51238K-QS USE XJ Version 13460 EGYPT 122.9 USD D51238K-QS USE XJ Version 13461 LIBYA 125 USD D51238K-QS USE XJ Version 13462 MOROCCO 110 EUR D51238K-QS USE XJ Version 13463 TUNISIA 120 EUR D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13456 IRAQ 132.3 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13457 JORDAN 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13458 LEBANON 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13459 SYRIA 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13460 EGYPT 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13461 LIBYA 131.3 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13462 MOROCCO 115.5 EUR D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13464 ALGERIA 139.76 USD D51256K-QS USE XJ VERSION 13456 IRAQ 183.8 USD "Bob Phillips" wrote: I completely understand how your data is laid out and the formula I gave you should do what you want. Show me the exact formula that you used. an example of the data would help too. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... It gave me an error which is #NUM though I checked it many times. The point is that in the countries list, the country is repeated many times, and in the machine model column, the machine is repeated many times, you need to create a lookup function which will search for the machine and then when it founds the machine search in another column for the country and then when both the machine and the country matches your search criteria you need to get the price from another column, which means basically that you have a two columns lookup procedure. Regards, "Bob Phillips" wrote: Which is exactly what we gave you. Did you try it? what happened when you did? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... Guys, It is not working, see I can pass you the files if possible just tell me how as I am new to this whole thing. The point is that I have many machines for one country and I have many countries, to give you an idea, I have seven coutries and for each country and I have more than three hundred machines prices. The system given me a dupm showing in one column the country name and in another column the machine name and then the price. I need a function that checks the country in one column and in the other columnn it checks the machine, if both are OK then it should give me the price which is in another column. Regards, Farah "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing dir ect) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
Thanks Bob, it is working great, but why do I need to press CTRL+SHIFT+ENTER
to evalluate this function Regards, Farah "Bob Phillips" wrote: This works fine for me =INDEX(C1:C100,MATCH(1,(B1:B100="LEBANON")*(A1:A10 0="D51238K-QS USE XJ Version 13458"),0)) although I do notice that all the machines are unique -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I attached below the data, I am sorry it is not tidy enough but the first column is the machine model, then you can see the country and then the price. D28886-QS Dewalt Die Grinder. 710W variable speed 13456 IRAQ 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13457 JORDAN 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13458 LEBANON 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13459 SYRIA 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13460 EGYPT 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13461 LIBYA 126 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13462 MOROCCO 116 EUR D28886-QS Dewalt Die Grinder. 710W variable speed 13463 TUNISIA 134 EUR D28886-QS Dewalt Die Grinder. 710W variable speed 13464 ALGERIA 140.36 USD D51238K-QS USE XJ Version 13457 JORDAN 122.9 USD D51238K-QS USE XJ Version 13458 LEBANON 122.9 USD D51238K-QS USE XJ Version 13459 SYRIA 117 USD D51238K-QS USE XJ Version 13460 EGYPT 122.9 USD D51238K-QS USE XJ Version 13461 LIBYA 125 USD D51238K-QS USE XJ Version 13462 MOROCCO 110 EUR D51238K-QS USE XJ Version 13463 TUNISIA 120 EUR D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13456 IRAQ 132.3 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13457 JORDAN 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13458 LEBANON 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13459 SYRIA 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13460 EGYPT 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13461 LIBYA 131.3 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13462 MOROCCO 115.5 EUR D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13464 ALGERIA 139.76 USD D51256K-QS USE XJ VERSION 13456 IRAQ 183.8 USD "Bob Phillips" wrote: I completely understand how your data is laid out and the formula I gave you should do what you want. Show me the exact formula that you used. an example of the data would help too. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... It gave me an error which is #NUM though I checked it many times. The point is that in the countries list, the country is repeated many times, and in the machine model column, the machine is repeated many times, you need to create a lookup function which will search for the machine and then when it founds the machine search in another column for the country and then when both the machine and the country matches your search criteria you need to get the price from another column, which means basically that you have a two columns lookup procedure. Regards, "Bob Phillips" wrote: Which is exactly what we gave you. Did you try it? what happened when you did? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... Guys, It is not working, see I can pass you the files if possible just tell me how as I am new to this whole thing. The point is that I have many machines for one country and I have many countries, to give you an idea, I have seven coutries and for each country and I have more than three hundred machines prices. The system given me a dupm showing in one column the country name and in another column the machine name and then the price. I need a function that checks the country in one column and in the other columnn it checks the machine, if both are OK then it should give me the price which is in another column. Regards, Farah "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing dir ect) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
Because the double condition test needs to evaluate to an array, and the way
that you tell hat to Excel is bey entering with Ctrl-Shift-Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... Thanks Bob, it is working great, but why do I need to press CTRL+SHIFT+ENTER to evalluate this function Regards, Farah "Bob Phillips" wrote: This works fine for me =INDEX(C1:C100,MATCH(1,(B1:B100="LEBANON")*(A1:A10 0="D51238K-QS USE XJ Version 13458"),0)) although I do notice that all the machines are unique -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I attached below the data, I am sorry it is not tidy enough but the first column is the machine model, then you can see the country and then the price. D28886-QS Dewalt Die Grinder. 710W variable speed 13456 IRAQ 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13457 JORDAN 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13458 LEBANON 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13459 SYRIA 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13460 EGYPT 137 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13461 LIBYA 126 USD D28886-QS Dewalt Die Grinder. 710W variable speed 13462 MOROCCO 116 EUR D28886-QS Dewalt Die Grinder. 710W variable speed 13463 TUNISIA 134 EUR D28886-QS Dewalt Die Grinder. 710W variable speed 13464 ALGERIA 140.36 USD D51238K-QS USE XJ Version 13457 JORDAN 122.9 USD D51238K-QS USE XJ Version 13458 LEBANON 122.9 USD D51238K-QS USE XJ Version 13459 SYRIA 117 USD D51238K-QS USE XJ Version 13460 EGYPT 122.9 USD D51238K-QS USE XJ Version 13461 LIBYA 125 USD D51238K-QS USE XJ Version 13462 MOROCCO 110 EUR D51238K-QS USE XJ Version 13463 TUNISIA 120 EUR D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13456 IRAQ 132.3 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13457 JORDAN 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13458 LEBANON 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13459 SYRIA 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13460 EGYPT 129 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13461 LIBYA 131.3 USD D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13462 MOROCCO 115.5 EUR D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13464 ALGERIA 139.76 USD D51256K-QS USE XJ VERSION 13456 IRAQ 183.8 USD "Bob Phillips" wrote: I completely understand how your data is laid out and the formula I gave you should do what you want. Show me the exact formula that you used. an example of the data would help too. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... It gave me an error which is #NUM though I checked it many times. The point is that in the countries list, the country is repeated many times, and in the machine model column, the machine is repeated many times, you need to create a lookup function which will search for the machine and then when it founds the machine search in another column for the country and then when both the machine and the country matches your search criteria you need to get the price from another column, which means basically that you have a two columns lookup procedure. Regards, "Bob Phillips" wrote: Which is exactly what we gave you. Did you try it? what happened when you did? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... Guys, It is not working, see I can pass you the files if possible just tell me how as I am new to this whole thing. The point is that I have many machines for one country and I have many countries, to give you an idea, I have seven coutries and for each country and I have more than three hundred machines prices. The system given me a dupm showing in one column the country name and in another column the machine name and then the price. I need a function that checks the country in one column and in the other columnn it checks the machine, if both are OK then it should give me the price which is in another column. Regards, Farah "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing dir ect) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for a value in an Array
Sorry to jump in here but I have a question that runs along the lines of the
original post of this thread but also has a different twist. In each cell of Page 1, A1:100 I want to search Page 2, A1:50 for a specific code. If that code is there then I want the amount in Column B of that same row on Page 2 to be returned. I presume the first part is easily handled by the first part of the INDEX formula below but how do I get it to return the correct value? "Aladin Akyurek" wrote: Probably it's easier to understand a bit faster version... =INDEX(C1:C100,MATCH(1,IF(A1:A100="country",IF(B1: B100="machine",1)),0)) which still needs to be confirmed with control+shift+enter. Jim May wrote: Bob, I'm getting closer and closer to understanding this stuff, but in this example there is one small piece I haven't got yet, Your Match() has 3 elements: 1) the 1, 2) the combination of the ranges (Col A and ColB as one parameter) 3) the 0, which is an exact match) So 1 (in this case) is the lookup value, hummmm.. not sure I'm getting the meaning here - I take 1 to be the value I'm looking for, confused Tks in Advance, Jim "Bob Phillips" wrote: =INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B10 0="machine"),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Farah" wrote in message ... I have a master price list were I have in column A the country name, and in column B the machine model number and in column C the price. Obviously you can find the same machine for many countries with different prices, I need a formula were I can extract for a certain country the price of a certain machine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching a limited amount of rows in a vertical array | Excel Discussion (Misc queries) | |||
searching in an array | Excel Discussion (Misc queries) | |||
Searching for a criteria in array of cells within an IF statement | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |