Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP combined with AND
Is it possible to nest the AND function with VLOOKUP? I think I have the
formula entered correctly, but get the dreaded #N/A. It may be my date in the lookup does not match my array, but I think I hve that corrected, yet it still doesn't work. Example of formula =VLOOKUP(AND(A19, B19, C19),A1355:G2244,5,FALSE) I have a patient number, date and time, from my current worksheet, and I pasted an array below to lookup the patient, date and time to match their heart rate. Thanks for any guidance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP combined with AND
Boolean functions do not work within VLOOKUP
The cells A19, B19, C19 hold Id number, date and time? And A1355 has the first ID, B1355 the first date, C1355 the first time Insert a helper column D; In D19 enter =A19&B19&C19 Enter A1355&B1355&C1355 in the new D1355 ; copy it down the column (the quick way is to double click the fill handle which is the small solid square in lower right corner of D1355 when that cell is selected) The lookup will be =VLOOKUP(D19),D1355:G2244,3,FALSE) best wishes -- let us know if this works -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Lost in Microbiology" wrote in message ... Is it possible to nest the AND function with VLOOKUP? I think I have the formula entered correctly, but get the dreaded #N/A. It may be my date in the lookup does not match my array, but I think I hve that corrected, yet it still doesn't work. Example of formula =VLOOKUP(AND(A19, B19, C19),A1355:G2244,5,FALSE) I have a patient number, date and time, from my current worksheet, and I pasted an array below to lookup the patient, date and time to match their heart rate. Thanks for any guidance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP combined with AND
try this ( use ctrl + shift + enter )
assumed Range (A1355:A2244 ) has patient number Range (B1355:B2244 ) has Date Range (C1355:C2244 ) has Time Heart rates are in Col F. =INDEX(A1355:G2244,MATCH (A19&B19&C19,A1355:A2244&B1355:B2244&C1355:C2244,0 ),5) On Nov 18, 10:43*pm, Lost in Microbiology wrote: Is it possible to nest the AND function with VLOOKUP? I think I have the formula entered correctly, but get the dreaded #N/A. It may be my date in the lookup does not match my array, but I think I hve that corrected, yet it still doesn't work. Example of formula =VLOOKUP(AND(A19, B19, C19),A1355:G2244,5,FALSE) I have a patient number, date and time, from my current worksheet, and I pasted an array below to lookup the patient, date and time to match their heart rate. Thanks for any guidance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP combined with AND
Are you trying to match A19 with a value in A1355:A2244
and at the same time match B19 with a value in b1355:b2244 and at the same time match C19 with a value in c1355:c2244??? If yes, then the first thing I would do is move that table to a different sheet. Too many things can go wrong if you keep your table on the same sheet as the data. After that change is made, you can use a formula that looks for a match in those columns in the other sheet. Saved from a previous post: If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Lost in Microbiology wrote: Is it possible to nest the AND function with VLOOKUP? I think I have the formula entered correctly, but get the dreaded #N/A. It may be my date in the lookup does not match my array, but I think I hve that corrected, yet it still doesn't work. Example of formula =VLOOKUP(AND(A19, B19, C19),A1355:G2244,5,FALSE) I have a patient number, date and time, from my current worksheet, and I pasted an array below to lookup the patient, date and time to match their heart rate. Thanks for any guidance! -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP combined with AND
All of your responses worked. Thanks, sometimes it is a matter of wrapping
your head around the problem in the right context, and my head is built for things that wiggle in a petri dish! Much appreciated all of you! "muddan madhu" wrote: try this ( use ctrl + shift + enter ) assumed Range (A1355:A2244 ) has patient number Range (B1355:B2244 ) has Date Range (C1355:C2244 ) has Time Heart rates are in Col F. =INDEX(A1355:G2244,MATCH (A19&B19&C19,A1355:A2244&B1355:B2244&C1355:C2244,0 ),5) On Nov 18, 10:43 pm, Lost in Microbiology wrote: Is it possible to nest the AND function with VLOOKUP? I think I have the formula entered correctly, but get the dreaded #N/A. It may be my date in the lookup does not match my array, but I think I hve that corrected, yet it still doesn't work. Example of formula =VLOOKUP(AND(A19, B19, C19),A1355:G2244,5,FALSE) I have a patient number, date and time, from my current worksheet, and I pasted an array below to lookup the patient, date and time to match their heart rate. Thanks for any guidance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup combined with Hlookup | Excel Worksheet Functions | |||
vlookup combined with AND-function | Excel Worksheet Functions | |||
vlookup and match combined? | Excel Worksheet Functions | |||
Combined VLOOKUP AND BETWEEN FUNCTION | Excel Worksheet Functions | |||
vlookup / len function combined | Excel Discussion (Misc queries) |