vlookup with 2 columns?

  • Thread starter Thread starter ben
  • Start date Start date
B

ben

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)
 
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
 
Back
Top