The following fomula is contained in cell C16 and returns the correct
values:

=VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE)

The following formula is contained in cell L18 and uses the result in
C16 as a lookup value in the first "match":

=INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21: M21,-1))

My promlem is that when I try to match on cell C16 my index formula
returns an error. However, when I change the index formula to
reference another cell (let's say cell D16) and I type in the value
shown in cell C16, the index formula works fine. I think my problem
has something to do with formatting because if I enter this:
D16=C16... the index formula fails once again.

See this about common problems with lookup formulas:

http://contextures.com/xlFunctions02.html#Trouble

--
Biff
Microsoft Excel MVP


<TWLove556694@gmail.com> wrote in message
news:884731fe-d606-4aa6-9d47-d2d79f12a23e@s50g2000hsb.googlegroups.com...
> The following fomula is contained in cell C16 and returns the correct
> values:
>
> =VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE)
>
> The following formula is contained in cell L18 and uses the result in
> C16 as a lookup value in the first "match":
>
> =INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21: M21,-1))
>
> My promlem is that when I try to match on cell C16 my index formula
> returns an error. However, when I change the index formula to
> reference another cell (let's say cell D16) and I type in the value
> shown in cell C16, the index formula works fine. I think my problem
> has something to do with formatting because if I enter this:
> D16=C16... the index formula fails once again.

So it sounds like your VLOOKUP returns a text formatted "number" which you
need to be a real number. Try adding +0 to your VLOOKUP to convert it to
numeric, i.e. change C16 to

=VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE)+0

"T. Valko" wrote:

> See this about common problems with lookup formulas:
>
> http://contextures.com/xlFunctions02.html#Trouble
>
> --
> Biff
> Microsoft Excel MVP
>
>
> <TWLove556694@gmail.com> wrote in message
> news:884731fe-d606-4aa6-9d47-d2d79f12a23e@s50g2000hsb.googlegroups.com...
> > The following fomula is contained in cell C16 and returns the correct
> > values:
> >
> > =VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE)
> >
> > The following formula is contained in cell L18 and uses the result in
> > C16 as a lookup value in the first "match":
> >
> > =INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21: M21,-1))
> >
> > My promlem is that when I try to match on cell C16 my index formula
> > returns an error. However, when I change the index formula to
> > reference another cell (let's say cell D16) and I type in the value
> > shown in cell C16, the index formula works fine. I think my problem
> > has something to do with formatting because if I enter this:
> > D16=C16... the index formula fails once again.
>
>
>