Re: Using VLOOKUP to draw data from two columns

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 ***, 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
.

Quantcast