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!

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!

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!

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

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!