Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with VLOOKUP
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 --------------------------------------------------------------------- 800 Smith Joe 2/25/1984 801 Smith Mary 8/15/1986 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with VLOOKUP
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 --------------------------------------------------------------------- 800 Smith Joe 2/25/1984 801 Smith Mary 8/15/1986 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with VLOOKUP
On Aug 22, 9:56*am, Claus Busch wrote:
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 --------------------------------------------------------------------- 800 * * Smith * * * Joe * * *2/25/1984 801 * * Smith * * * Mary * *8/15/1986 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with VLOOKUP
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with VLOOKUP
"Steve Ross" wrote:
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup problem - unable to get the vlookup property | Excel Programming | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel |