Need Help with VLOOKUP

  • Thread starter Thread starter Steve Ross
  • Start date Start date
S

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
 
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
Click to expand...

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
 
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 -
Click to expand...

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?
 
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?
Click to expand...

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
 
Steve Ross said:
=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?
Click to expand...

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.
 
Back
Top