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.

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.

=INDEX(C1:C100,MATCH(1,(A1:A100="country")*(B1:B100="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" <Farah@discussions.microsoft.com> wrote in message
news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> 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.

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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > 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.
>
>
>

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" <JimMay@discussions.microsoft.com> wrote in message
news:99D4B1A8-592D-499C-AF82-FBE36F02C1AA@microsoft.com...
> 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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> > news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > > 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.
> >
> >
> >

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" <JimMay@discussions.microsoft.com> wrote in message
> news:99D4B1A8-592D-499C-AF82-FBE36F02C1AA@microsoft.com...
> > 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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> > > news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > > > 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.
> > >
> > >
> > >
>
>
>

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:B100="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" <Farah@discussions.microsoft.com> wrote in message
>> news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
>>> 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.
>>
>>

.... 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" <JimMay@discussions.microsoft.com> wrote in message
news:CE847301-20D1-43D9-AE6B-2A04EDDE2370@microsoft.com...
> 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" <JimMay@discussions.microsoft.com> wrote in message
> > news:99D4B1A8-592D-499C-AF82-FBE36F02C1AA@microsoft.com...
> > > 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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> > > > news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >

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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> >> news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> >>> 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.
> >>
> >>
>

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" <Farah@discussions.microsoft.com> wrote in message
news:221CAA4B-D993-4A11-BFEA-DCD1CBEFA984@microsoft.com...
> 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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> > >> news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > >>> 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.
> > >>
> > >>
> >

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" <Farah@discussions.microsoft.com> wrote in message
> news:221CAA4B-D993-4A11-BFEA-DCD1CBEFA984@microsoft.com...
> > 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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> > > >> news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > > >>> 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.
> > > >>
> > > >>
> > >
>
>
>

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" <Farah@discussions.microsoft.com> wrote in message
news:273573A4-4E87-4E65-B8A0-9D2AA2D4CC6F@microsoft.com...
> 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" <Farah@discussions.microsoft.com> wrote in message
> > news:221CAA4B-D993-4A11-BFEA-DCD1CBEFA984@microsoft.com...
> > > 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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> > > > >> news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > > > >>> 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.
> > > > >>
> > > > >>
> > > >
> >
> >
> >

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" <Farah@discussions.microsoft.com> wrote in message
> news:273573A4-4E87-4E65-B8A0-9D2AA2D4CC6F@microsoft.com...
> > 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" <Farah@discussions.microsoft.com> wrote in message
> > > news:221CAA4B-D993-4A11-BFEA-DCD1CBEFA984@microsoft.com...
> > > > 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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> > > > > >> news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > > > > >>> 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.
> > > > > >>
> > > > > >>
> > > > >
> > >
> > >
> > >
>
>
>

This works fine for me

=INDEX(C1:C100,MATCH(1,(B1:B100="LEBANON")*(A1:A100="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" <Farah@discussions.microsoft.com> wrote in message
news:713D90AA-3123-417A-8581-0547E1E07C10@microsoft.com...
> 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" <Farah@discussions.microsoft.com> wrote in message
> > news:273573A4-4E87-4E65-B8A0-9D2AA2D4CC6F@microsoft.com...
> > > 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" <Farah@discussions.microsoft.com> wrote in message
> > > > news:221CAA4B-D993-4A11-BFEA-DCD1CBEFA984@microsoft.com...
> > > > > 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:B100="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" <Farah@discussions.microsoft.com> wrote in message
> > > > > > >> news:E3812033-F04C-47D5-9CA2-E8C9459C9EAE@microsoft.com...
> > > > > > >>> 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.
> > > > > > >>
> > > > > > >>
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >