Prev: Can I SUM column C IF Column A=X AND Column D=Y?
Next: Automatic Update in Multiple Worksheets
From: Don B on 9 Oct 2009 10:14
{=IF(K8=AA11:AA21,VLOOKUP(K8,'Regular Assessment Table'!
I2:J12,2,FALSE))}

The above formula is looking up a K8 text content and returning a
vlookup value from a table. Part of the text I enter is "104(a)
Citation" and the formula return the correct value. But when I enter
"104(d)(1) Citation" the formula returns FALSE. For the life of me I
cannot determine why this is happening. Any ideas?
From: Luke M on 9 Oct 2009 11:16
The first part of your formula is not doing what you think it's doing. even
though you wrote "K8=aa11:aa21", the next part of the formula requires a
single value, so its only passing the first value from the array (in this
case, false). As you formula as no criteria given for what to do with a false
statement, the function results to false.

If you are really wanting to check if K8 is found anywhere in aa1:aa21, and
if not, return a blank cell, correct syntax would be:

=IF(ISNUMBER(MATCH(K8,AA11:AA21,0)),VLOOKUP(K8,
'Regular Assessment Table'!I2:J12,2,FALSE),"")

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Don B" wrote:

> {=IF(K8=AA11:AA21,VLOOKUP(K8,'Regular Assessment Table'!
> I2:J12,2,FALSE))}
>
> The above formula is looking up a K8 text content and returning a
> vlookup value from a table. Part of the text I enter is "104(a)
> Citation" and the formula return the correct value. But when I enter
> "104(d)(1) Citation" the formula returns FALSE. For the life of me I
> cannot determine why this is happening. Any ideas?
>
From: Don B on 9 Oct 2009 12:47
On Oct 9, 11:16 am, Luke M <Lu...(a)discussions.microsoft.com> wrote:
> The first part of your formula is not doing what you think it's doing. even
> though you wrote "K8=aa11:aa21", the next part of the formula requires a
> single value, so its only passing the first value from the array (in this
> case, false). As you formula as no criteria given for what to do with a false
> statement, the function results to false.
>
> If you are really wanting to check if K8 is found anywhere in aa1:aa21, and
> if not, return a blank cell, correct syntax would be:
>
> =IF(ISNUMBER(MATCH(K8,AA11:AA21,0)),VLOOKUP(K8,
> 'Regular Assessment Table'!I2:J12,2,FALSE),"")
>
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
>
> "Don B" wrote:
> > {=IF(K8=AA11:AA21,VLOOKUP(K8,'Regular Assessment Table'!
> > I2:J12,2,FALSE))}
>
> > The above formula is looking up a K8 text content and returning a
> > vlookup value from a table.  Part of the text I enter is "104(a)
> > Citation" and the formula return the correct value.  But when I enter
> > "104(d)(1) Citation" the formula returns FALSE.  For the life of me I
> > cannot determine why this is happening.  Any ideas?- Hide quoted text -
>
> - Show quoted text -

Thanks Luke the formula worked like a charm. I have another formula I
have to add to search more criteria hopefully I can get it right
now.

Thanks a lot
Don
From: Don B on 15 Oct 2009 09:02
On Oct 9, 12:47 pm, Don B <Braenovich....(a)DOL.GOV> wrote:
> On Oct 9, 11:16 am, Luke M <Lu...(a)discussions.microsoft.com> wrote:
>
>
>
>
>
> > The first part of your formula is not doing what you think it's doing. even
> > though you wrote "K8=aa11:aa21", the next part of the formula requires a
> > single value, so its only passing the first value from the array (in this
> > case, false). As you formula as no criteria given for what to do with a false
> > statement, the function results to false.
>
> > If you are really wanting to check if K8 is found anywhere in aa1:aa21, and
> > if not, return a blank cell, correct syntax would be:
>
> > =IF(ISNUMBER(MATCH(K8,AA11:AA21,0)),VLOOKUP(K8,
> > 'Regular Assessment Table'!I2:J12,2,FALSE),"")
>
> > --
> > Best Regards,
>
> > Luke M
> > *Remember to click "yes" if this post helped you!*
>
> > "Don B" wrote:
> > > {=IF(K8=AA11:AA21,VLOOKUP(K8,'Regular Assessment Table'!
> > > I2:J12,2,FALSE))}
>
> > > The above formula is looking up a K8 text content and returning a
> > > vlookup value from a table.  Part of the text I enter is "104(a)
> > > Citation" and the formula return the correct value.  But when I enter
> > > "104(d)(1) Citation" the formula returns FALSE.  For the life of me I
> > > cannot determine why this is happening.  Any ideas?- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks Luke the formula worked like a charm.  I have another formula I
> have to add to search more criteria hopefully I can get it right
> now.
>
> Thanks a lot
> Don- Hide quoted text -
>
> - Show quoted text -

I tried to modify the above formula to be able to read two lookup
values and return the corrrect lookupvalue based upon this
information. My new formula is below. Well needless to say it will
not work.
{=IF(OR(K8=AA11:AA21,ISTEXT(K10)),0,VLOOKUP(K8,'Regular Assessment
Table'!I2:J12,2,FALSE))}

What I am trying to accomplish is to be able to look at two different
lookup values (K8 & K10) and return the vlookup value calculation
accordingly. There is always a lookup value in K8. Normally the K10
value is blank but occassionally there will be a time when there is a
K8 and K10 value to calculate. For whatever reason I cannot get the
formula right. Each of the K8 and K10 values are in a drop down list
but the first cell in the K10 list is blank. When I did the
validation list for K10 I unchecked the box to ignore blanks. I
thought that might be my problem but it was not. Let me also say that
the K8 &K10 lookup values are text strings. What am I doing wrong,Can
anybody get me back on the right path? Thanks Don
 | 
Pages: 1
Prev: Can I SUM column C IF Column A=X AND Column D=Y?
Next: Automatic Update in Multiple Worksheets