vlookup with 2 columns?
 

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

vlookup with 2 columns?
 

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

vlookup with 2 columns?
 

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)

vlookup with 2 columns?
 

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)

vlookup with 2 columns?
 

Thanks!

Bob Phillips wrote:
Take a look at this

http://tinyurl.com/6bc2yt