Need Help with VLOOKUP

[Steve Ross]

I'm using this VLookup formula, =VLOOKUP(B19,$B$698:$D$1318,3,FALSE),
to match birth dates with names. The problem I'm running into is when
I have 2 of the same LAST names. This formula tends to populate the
associated cells with only the first date. Cell D6 should read
8/15/1986 as reference in D801 below.

I've tried nesting but it didn't work. Any ideas on how to fix this
problem?

B C D E
5 Smith Joe 2/25/1984 28
6 Smith Mary 2/25/1984 28

 

[Claus Busch]

Hi Steve,

Am Wed, 22 Aug 2012 09:40:39 -0700 (PDT) schrieb Steve Ross:

I'm using this VLookup formula, =VLOOKUP(B19,$B$698:$D$1318,3,FALSE),
to match birth dates with names. The problem I'm running into is when
I have 2 of the same LAST names. This formula tends to populate the
associated cells with only the first date. Cell D6 should read
8/15/1986 as reference in D801 below.

I've tried nesting but it didn't work. Any ideas on how to fix this
problem?

B C D E
5 Smith Joe 2/25/1984 28
6 Smith Mary 2/25/1984 28

try:
=INDEX($D$698:$D$1318,MATCH(B19&C19,$B$698:$B$1318&$C$698:$C$1318,0))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch

 

[Steve Ross]

Hi Steve,

Am Wed, 22 Aug 2012 09:40:39 -0700 (PDT) schrieb Steve Ross:









try:
=INDEX($D$698:$D$1318,MATCH(B19&C19,$B$698:$B$1318&$C$698:$C$1318,0))
and enter the array formula with CTRL+Shift+Enter

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2- Hide quoted text -

- Show quoted text -

Thank you! That worked perfect. Not sure how it works but it does.
What is the reason one must enter CNTL+Shift+Enter? What does that do?

 

[Claus Busch]

Hi Steve,

Am Wed, 22 Aug 2012 11:07:57 -0700 (PDT) schrieb Steve Ross:

What is the reason one must enter CNTL+Shift+Enter? What does that do?

CTRL+Shift+Enter put curved brackets at the start and at the end of the
formula. Then the formula is an array formula.


Regards
Claus Busch

 

[joeu2004]

=INDEX($D$698:$D$1318,MATCH(B19&C19,$B$698:$B$1318&$C$698:$C$1318,0))
and enter the array formula with CTRL+Shift+Enter [....]
What is the reason one must enter CNTL+Shift+Enter?
What does that do?

It causes the formula to be "array-entered".

The expression $B$698:$B$1318&$C$698:$C$1318 is intended to build an array
for the 2nd parameter.

Since the 2nd parameter is supposed to be an array or range, you would think
Excel would know what to do with the expression. And in some contexts, it
does. But sometimes, Excel needs help in determining our intent.

So sometimes (difficult to say when), we must "array-enter" the formula by
pressing ctrl+shift+Enter. Excel will identify the array-entered formula by
displaying it with curly braces around it, e.g. {=formula}. We cannot type
the curly braces ourselves in this context. So we must remembrer to press
ctrl+shift+Enter every time we edit and re-enter the formula.

Caveat: Often, if we forget to press ctrl+shift+Enter when we should, Excel
will return a #VALUE error to let us know something is wrong. But in some
contexts, Excel might interpret the array parameter in a non-array-entered
formula as single-valued. So there is no Excel error. This can be
misleading.

For that reason, I avoid array-entered formulas -- although it might be
needed in this case. A possible alternative is to put the formula
=B698&C698 into column E (e.g.) and use the follow normally-entered formula:

=INDEX($D$698:$D$1318,MATCH(B19&C19,$E$698:$E$1318,0))

It is also more efficient in both time and space.