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
|