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