Hello,

I am doing a VLOOKUP which is working nicely, but I had this idea where the
user could specify in the sheet data from 1 column and then data from
another column and where you have both in the row I would get other data in
the row. Is there anyway of doing that?

E.g. if the data was like I have laid out below and the user specified A1
and X2 I could get at the data 20 or 21 but I wouldn't want the row before that.
A1 X1 10 11
A1 X2 20 21
B1 Y1 11 22
B2 Y2 22 33

Thanks.
Bn

If the data to return is numeric as is demonstrated in your sample *and* the
combination of A1+X2 is unique:

=SUMPRODUCT(--(A1:A4="A1"),--(B1:B4="X2"),C1:C4)

Or, this generic version works for any data type.

Array entered** :

=INDEX(C1:C4,MATCH(1,(A1:A4="A1")*(B1:B4="X2"),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"ben" wrote in message
...
Hello,

I am doing a VLOOKUP which is working nicely, but I had this idea where
the user could specify in the sheet data from 1 column and then data from
another column and where you have both in the row I would get other data
in the row. Is there anyway of doing that?

E.g. if the data was like I have laid out below and the user specified A1
and X2 I could get at the data 20 or 21 but I wouldn't want the row before
that.
A1 X1 10 11
A1 X2 20 21
B1 Y1 11 22
B2 Y2 22 33
Thanks.
Bn

So far I have just been experimenting with the first option and it works. I
am not familiar enough with the notation to know why though. A1:A4 and B1:B4
define the range (array) of cells I want checked and putting the array = to
something implies a condition matching the cell I want. What does the '--'
before the condition mean? From what I read on SUMPRODUCT each array needs
to be the same size, but I would have thought that the below example may
have them not being the same size unless the '--' has something to do with
it. Would you be able to clarify this for me?

Thanks,
Bn

T. Valko wrote:
If the data to return is numeric as is demonstrated in your sample *and* the
combination of A1+X2 is unique:

=SUMPRODUCT(--(A1:A4="A1"),--(B1:B4="X2"),C1:C4)

Or, this generic version works for any data type.

Array entered** :

=INDEX(C1:C4,MATCH(1,(A1:A4="A1")*(B1:B4="X2"),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Take a look at this

http://tinyurl.com/6bc2yt

--
__________________________________
HTH

Bob

"ben" wrote in message
...
So far I have just been experimenting with the first option and it works.
I am not familiar enough with the notation to know why though. A1:A4 and
B1:B4 define the range (array) of cells I want checked and putting the
array = to something implies a condition matching the cell I want. What
does the '--' before the condition mean? From what I read on SUMPRODUCT
each array needs to be the same size, but I would have thought that the
below example may have them not being the same size unless the '--' has
something to do with it. Would you be able to clarify this for me?

Thanks,
Bn

T. Valko wrote:
If the data to return is numeric as is demonstrated in your sample *and*
the combination of A1+X2 is unique:

=SUMPRODUCT(--(A1:A4="A1"),--(B1:B4="X2"),C1:C4)

Or, this generic version works for any data type.

Array entered** :

=INDEX(C1:C4,MATCH(1,(A1:A4="A1")*(B1:B4="X2"),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Thanks!

Bob Phillips wrote:
Take a look at this

http://tinyurl.com/6bc2yt