Using VLOOKUP to draw data from two columns

It seems that others have asked different iterations of this question on this
board, but when trying to implement the solutions given within these posts,
it doesn't seem to fit my purposes.

I am trying to create a VLOOKUP function for two values that are returned
from two different drop boxes -- one dropbox is for the year, the other is
for the quarter. These dropboxes have linked cells, A1 and B1, so that when
two values are chosen (say, 2005 and Q1), A1 and B1 show "2005" and "Q1",
respectively.

Lower down in the sheet, I have a simple table of data with three columns.
The first, B50:B56, contains the year (2005, 2006, etc.). The second,
C50:C65, contains the associated Quarter (Q1, Q2, etc.). The third column,
D50:D65 holds the monthly period (Jan-Mar 05, Apr-Jun 05, etc.).

This creates some duplication of data in the first to columns, as such:

COL B COL C COL D
2005 Q1 Jan-Mar 05
2005 Q2 Apr-Jun 05
2005 Q3 Jul-Oct 05
2005 Q4 Nov-Dec 05
2006 Q1 Jan-Mar 06
.... ... ...

I would like to put a VLOOKUP formula in C1 that looks at the values
returned from the dropbox in A1 and B1, and find the corresponding row match
to these two values in D50:D65.

Many thanks in advance, and I apologize if this type of function is already
covered somewhere else on the board.

Try...

=INDEX(D50:D65,MATCH(1,(B50:B65=A1)*(C50:C65=B1),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <386766CE-EC63-44EE-9A36-80BE6A915916@microsoft.com>,
Dan <Dan@discussions.microsoft.com> wrote:

> It seems that others have asked different iterations of this question on this
> board, but when trying to implement the solutions given within these posts,
> it doesn't seem to fit my purposes.
>
> I am trying to create a VLOOKUP function for two values that are returned
> from two different drop boxes -- one dropbox is for the year, the other is
> for the quarter. These dropboxes have linked cells, A1 and B1, so that when
> two values are chosen (say, 2005 and Q1), A1 and B1 show "2005" and "Q1",
> respectively.
>
> Lower down in the sheet, I have a simple table of data with three columns.
> The first, B50:B56, contains the year (2005, 2006, etc.). The second,
> C50:C65, contains the associated Quarter (Q1, Q2, etc.). The third column,
> D50:D65 holds the monthly period (Jan-Mar 05, Apr-Jun 05, etc.).
>
> This creates some duplication of data in the first to columns, as such:
>
> COL B COL C COL D
> 2005 Q1 Jan-Mar 05
> 2005 Q2 Apr-Jun 05
> 2005 Q3 Jul-Oct 05
> 2005 Q4 Nov-Dec 05
> 2006 Q1 Jan-Mar 06
> ... ... ...
>
> I would like to put a VLOOKUP formula in C1 that looks at the values
> returned from the dropbox in A1 and B1, and find the corresponding row match
> to these two values in D50:D65.
>
> Many thanks in advance, and I apologize if this type of function is already
> covered somewhere else on the board.

First, sometimes you wrote 56 (B56) and sometimes 65 (as in C65 and D65). I'm
assuming that 56 was a typo in the post.

=index(D50:d65,match(1,(b50:b65=A1)*(c50:c65=b1),0))


Dan wrote:
>
> It seems that others have asked different iterations of this question on this
> board, but when trying to implement the solutions given within these posts,
> it doesn't seem to fit my purposes.
>
> I am trying to create a VLOOKUP function for two values that are returned
> from two different drop boxes -- one dropbox is for the year, the other is
> for the quarter. These dropboxes have linked cells, A1 and B1, so that when
> two values are chosen (say, 2005 and Q1), A1 and B1 show "2005" and "Q1",
> respectively.
>
> Lower down in the sheet, I have a simple table of data with three columns.
> The first, B50:B56, contains the year (2005, 2006, etc.). The second,
> C50:C65, contains the associated Quarter (Q1, Q2, etc.). The third column,
> D50:D65 holds the monthly period (Jan-Mar 05, Apr-Jun 05, etc.).
>
> This creates some duplication of data in the first to columns, as such:
>
> COL B COL C COL D
> 2005 Q1 Jan-Mar 05
> 2005 Q2 Apr-Jun 05
> 2005 Q3 Jul-Oct 05
> 2005 Q4 Nov-Dec 05
> 2006 Q1 Jan-Mar 06
> ... ... ...
>
> I would like to put a VLOOKUP formula in C1 that looks at the values
> returned from the dropbox in A1 and B1, and find the corresponding row match
> to these two values in D50:D65.
>
> Many thanks in advance, and I apologize if this type of function is already
> covered somewhere else on the board.

--

Dave Peterson

ps.

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Dave Peterson wrote:
>
> First, sometimes you wrote 56 (B56) and sometimes 65 (as in C65 and D65). I'm
> assuming that 56 was a typo in the post.
>
> =index(D50:d65,match(1,(b50:b65=A1)*(c50:c65=b1),0))
>
> Dan wrote:
> >
> > It seems that others have asked different iterations of this question on this
> > board, but when trying to implement the solutions given within these posts,
> > it doesn't seem to fit my purposes.
> >
> > I am trying to create a VLOOKUP function for two values that are returned
> > from two different drop boxes -- one dropbox is for the year, the other is
> > for the quarter. These dropboxes have linked cells, A1 and B1, so that when
> > two values are chosen (say, 2005 and Q1), A1 and B1 show "2005" and "Q1",
> > respectively.
> >
> > Lower down in the sheet, I have a simple table of data with three columns.
> > The first, B50:B56, contains the year (2005, 2006, etc.). The second,
> > C50:C65, contains the associated Quarter (Q1, Q2, etc.). The third column,
> > D50:D65 holds the monthly period (Jan-Mar 05, Apr-Jun 05, etc.).
> >
> > This creates some duplication of data in the first to columns, as such:
> >
> > COL B COL C COL D
> > 2005 Q1 Jan-Mar 05
> > 2005 Q2 Apr-Jun 05
> > 2005 Q3 Jul-Oct 05
> > 2005 Q4 Nov-Dec 05
> > 2006 Q1 Jan-Mar 06
> > ... ... ...
> >
> > I would like to put a VLOOKUP formula in C1 that looks at the values
> > returned from the dropbox in A1 and B1, and find the corresponding row match
> > to these two values in D50:D65.
> >
> > Many thanks in advance, and I apologize if this type of function is already
> > covered somewhere else on the board.
>
> --
>
> Dave Peterson

--

Dave Peterson

Much obliged to you both for your help. I don't know a lot about index/match
array formulas, but this seems to make a lot of intuitive sense -- if I
understand it, the formula looks in column D50:D65 to find an exact row match
to the values that appear in both A1 and B1 in the A and B columns.

The only problem I seem to have is that the formula returns a #N/A error.

To test out what was going wrong, I manually typed "2005" and "Q1" in cells
C1 and D1, and changed the formula to:

=index(D50:D65,match(1,(B50:B65=C1)*(C50:C65=D1),0))

and it returned the correct value, "Apr-Jun 05."

I then made C1=A1 and D1=B1, and the formula returned a #N/A error. (I
ensured that I pressed Ctr-Shift-Enter when I changed the formula to account
for the fact that it is an array).

I'm thinking this is just a quirk that the formula doesn't recognize linked
cells to combo dropdown boxes. Do you have a suggestion to fix this error?

Again, many thanks for your help this far -- it's been enormously helpful --
and thanks for any further information you might have.

Dan

"Dave Peterson" wrote:

> First, sometimes you wrote 56 (B56) and sometimes 65 (as in C65 and D65). I'm
> assuming that 56 was a typo in the post.
>
> =index(D50:d65,match(1,(b50:b65=A1)*(c50:c65=b1),0))
>
>
> Dan wrote:
> >
> > It seems that others have asked different iterations of this question on this
> > board, but when trying to implement the solutions given within these posts,
> > it doesn't seem to fit my purposes.
> >
> > I am trying to create a VLOOKUP function for two values that are returned
> > from two different drop boxes -- one dropbox is for the year, the other is
> > for the quarter. These dropboxes have linked cells, A1 and B1, so that when
> > two values are chosen (say, 2005 and Q1), A1 and B1 show "2005" and "Q1",
> > respectively.
> >
> > Lower down in the sheet, I have a simple table of data with three columns.
> > The first, B50:B56, contains the year (2005, 2006, etc.). The second,
> > C50:C65, contains the associated Quarter (Q1, Q2, etc.). The third column,
> > D50:D65 holds the monthly period (Jan-Mar 05, Apr-Jun 05, etc.).
> >
> > This creates some duplication of data in the first to columns, as such:
> >
> > COL B COL C COL D
> > 2005 Q1 Jan-Mar 05
> > 2005 Q2 Apr-Jun 05
> > 2005 Q3 Jul-Oct 05
> > 2005 Q4 Nov-Dec 05
> > 2006 Q1 Jan-Mar 06
> > ... ... ...
> >
> > I would like to put a VLOOKUP formula in C1 that looks at the values
> > returned from the dropbox in A1 and B1, and find the corresponding row match
> > to these two values in D50:D65.
> >
> > Many thanks in advance, and I apologize if this type of function is already
> > covered somewhere else on the board.
>
> --
>
> Dave Peterson
>

My first guess is that you have a mismatch between the values in B50:B65 and
what's in C1.

If my years are really numbers in one of those locations and are text in the
other area, then you'll have a mismatch.

I think I'd fix it by making sure the years are numeric in both spots.

One way to fix this is:

Copy an empty cell
select that data validation range
edit|paste special|Add


Dan wrote:
>
> Much obliged to you both for your help. I don't know a lot about index/match
> array formulas, but this seems to make a lot of intuitive sense -- if I
> understand it, the formula looks in column D50:D65 to find an exact row match
> to the values that appear in both A1 and B1 in the A and B columns.
>
> The only problem I seem to have is that the formula returns a #N/A error.
>
> To test out what was going wrong, I manually typed "2005" and "Q1" in cells
> C1 and D1, and changed the formula to:
>
> =index(D50:D65,match(1,(B50:B65=C1)*(C50:C65=D1),0))
>
> and it returned the correct value, "Apr-Jun 05."
>
> I then made C1=A1 and D1=B1, and the formula returned a #N/A error. (I
> ensured that I pressed Ctr-Shift-Enter when I changed the formula to account
> for the fact that it is an array).
>
> I'm thinking this is just a quirk that the formula doesn't recognize linked
> cells to combo dropdown boxes. Do you have a suggestion to fix this error?
>
> Again, many thanks for your help this far -- it's been enormously helpful --
> and thanks for any further information you might have.
>
> Dan
>
> "Dave Peterson" wrote:
>
> > First, sometimes you wrote 56 (B56) and sometimes 65 (as in C65 and D65). I'm
> > assuming that 56 was a typo in the post.
> >
> > =index(D50:d65,match(1,(b50:b65=A1)*(c50:c65=b1),0))
> >
> >
> > Dan wrote:
> > >
> > > It seems that others have asked different iterations of this question on this
> > > board, but when trying to implement the solutions given within these posts,
> > > it doesn't seem to fit my purposes.
> > >
> > > I am trying to create a VLOOKUP function for two values that are returned
> > > from two different drop boxes -- one dropbox is for the year, the other is
> > > for the quarter. These dropboxes have linked cells, A1 and B1, so that when
> > > two values are chosen (say, 2005 and Q1), A1 and B1 show "2005" and "Q1",
> > > respectively.
> > >
> > > Lower down in the sheet, I have a simple table of data with three columns.
> > > The first, B50:B56, contains the year (2005, 2006, etc.). The second,
> > > C50:C65, contains the associated Quarter (Q1, Q2, etc.). The third column,
> > > D50:D65 holds the monthly period (Jan-Mar 05, Apr-Jun 05, etc.).
> > >
> > > This creates some duplication of data in the first to columns, as such:
> > >
> > > COL B COL C COL D
> > > 2005 Q1 Jan-Mar 05
> > > 2005 Q2 Apr-Jun 05
> > > 2005 Q3 Jul-Oct 05
> > > 2005 Q4 Nov-Dec 05
> > > 2006 Q1 Jan-Mar 06
> > > ... ... ...
> > >
> > > I would like to put a VLOOKUP formula in C1 that looks at the values
> > > returned from the dropbox in A1 and B1, and find the corresponding row match
> > > to these two values in D50:D65.
> > >
> > > Many thanks in advance, and I apologize if this type of function is already
> > > covered somewhere else on the board.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson