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: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.

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.

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.

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.

.... 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.

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.

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.

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.

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.