I have a list (~1500 entries) that I need to match patients with their
specimen. The problem comes where a single patient will have multiple
entries. Then it only finds the first occurrence and not a subsequent sample.

Is there an if then statement or a way to use 2 columns for criteria in a
vlookup? I want to match a patient medical record number and a received date
tofind the result.

Here is an example of my data:
Name MRN Accession Rec Date Final Result
John Doe 12345 07-000-0001 1/1/2007 No Virus
John Doe 12345 07-020-0000 1/20/2007 RSV
Jane Doe 3456 07-000-0002 1/1/2007 No Virus

I have 2 lists like this, one for one procedure and another for another
procedure, and I have to match the information to determine specificity of
the tests. Any help is greatly appreciated!

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:

I have a list (~1500 entries) that I need to match patients with their
specimen. The problem comes where a single patient will have multiple
entries. Then it only finds the first occurrence and not a subsequent sample.

Is there an if then statement or a way to use 2 columns for criteria in a
vlookup? I want to match a patient medical record number and a received date
tofind the result.

Here is an example of my data:
Name MRN Accession Rec Date Final Result
John Doe 12345 07-000-0001 1/1/2007 No Virus
John Doe 12345 07-020-0000 1/20/2007 RSV
Jane Doe 3456 07-000-0002 1/1/2007 No Virus

I have 2 lists like this, one for one procedure and another for another
procedure, and I have to match the information to determine specificity of
the tests. Any help is greatly appreciated!

--

Dave Peterson

Dave:

This is the formula I inputed:
=INDEX(Vlookup!$A$1578:$M$4802,MATCH(B332,Vlookup! $A$1578:$A$4802,0)*MATCH(LEFT(F332,6),Vlookup!$D$1 578:$D$4802,0),13)

I did hit ctrl-shift-enter and got the brackets correctly. However the
result was a #N/A. When I went to check, there should have been a result. Any
thoughts on what I did wrong?

Thanks.

"Dave Peterson" wrote:

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:

I have a list (~1500 entries) that I need to match patients with their
specimen. The problem comes where a single patient will have multiple
entries. Then it only finds the first occurrence and not a subsequent sample.

Is there an if then statement or a way to use 2 columns for criteria in a
vlookup? I want to match a patient medical record number and a received date
tofind the result.

Here is an example of my data:
Name MRN Accession Rec Date Final Result
John Doe 12345 07-000-0001 1/1/2007 No Virus
John Doe 12345 07-020-0000 1/20/2007 RSV
Jane Doe 3456 07-000-0002 1/1/2007 No Virus

I have 2 lists like this, one for one procedure and another for another
procedure, and I have to match the information to determine specificity of
the tests. Any help is greatly appreciated!

--

Dave Peterson

I'd just use column M in the index and drop the 13, but that's not the problem:

=INDEX(Vlookup!$M$1578:$M$4802,
MATCH(1,(B332=Vlookup!$A$1578:$A$4802)
*(LEFT(F332,6)=Vlookup!$D$1578:$D$4802),0))

Still array entered, too.




Lost in Microbiology wrote:

Dave:

This is the formula I inputed:
=INDEX(Vlookup!$A$1578:$M$4802,MATCH(B332,Vlookup! $A$1578:$A$4802,0)*MATCH(LEFT(F332,6),Vlookup!$D$1 578:$D$4802,0),13)

I did hit ctrl-shift-enter and got the brackets correctly. However the
result was a #N/A. When I went to check, there should have been a result. Any
thoughts on what I did wrong?

Thanks.

"Dave Peterson" wrote:

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:

I have a list (~1500 entries) that I need to match patients with their
specimen. The problem comes where a single patient will have multiple
entries. Then it only finds the first occurrence and not a subsequent sample.

Is there an if then statement or a way to use 2 columns for criteria in a
vlookup? I want to match a patient medical record number and a received date
tofind the result.

Here is an example of my data:
Name MRN Accession Rec Date Final Result
John Doe 12345 07-000-0001 1/1/2007 No Virus
John Doe 12345 07-020-0000 1/20/2007 RSV
Jane Doe 3456 07-000-0002 1/1/2007 No Virus

I have 2 lists like this, one for one procedure and another for another
procedure, and I have to match the information to determine specificity of
the tests. Any help is greatly appreciated!

--

Dave Peterson


--

Dave Peterson

I actually copy and pasted your response, and saw the difference in the index
array to only searh one column. Was there another error? I am still getting
an #N/A response. Could the data type be an issue? They are general fields
right now, but I could change them to number or text fields? Would that work?
I am getting a little frustrated at this point. Thanks so much for all of you
help.

"Dave Peterson" wrote:

I'd just use column M in the index and drop the 13, but that's not the problem:

=INDEX(Vlookup!$M$1578:$M$4802,
MATCH(1,(B332=Vlookup!$A$1578:$A$4802)
*(LEFT(F332,6)=Vlookup!$D$1578:$D$4802),0))

Still array entered, too.




Lost in Microbiology wrote:

Dave:

This is the formula I inputed:
=INDEX(Vlookup!$A$1578:$M$4802,MATCH(B332,Vlookup! $A$1578:$A$4802,0)*MATCH(LEFT(F332,6),Vlookup!$D$1 578:$D$4802,0),13)

I did hit ctrl-shift-enter and got the brackets correctly. However the
result was a #N/A. When I went to check, there should have been a result. Any
thoughts on what I did wrong?

Thanks.

"Dave Peterson" wrote:

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:

I have a list (~1500 entries) that I need to match patients with their
specimen. The problem comes where a single patient will have multiple
entries. Then it only finds the first occurrence and not a subsequent sample.

Is there an if then statement or a way to use 2 columns for criteria in a
vlookup? I want to match a patient medical record number and a received date
tofind the result.

Here is an example of my data:
Name MRN Accession Rec Date Final Result
John Doe 12345 07-000-0001 1/1/2007 No Virus
John Doe 12345 07-020-0000 1/20/2007 RSV
Jane Doe 3456 07-000-0002 1/1/2007 No Virus

I have 2 lists like this, one for one procedure and another for another
procedure, and I have to match the information to determine specificity of
the tests. Any help is greatly appreciated!

--

Dave Peterson


--

Dave Peterson

The =match() portion of the formula you posted wasn't right.

Did you change the rest of the formula? I made other changes.

And the format of the cells isn't important. It's the value in the cells.

But do remember that =left() will be returning text--not a number. Could that
be the problem?

What's in F332?


Lost in Microbiology wrote:

I actually copy and pasted your response, and saw the difference in the index
array to only searh one column. Was there another error? I am still getting
an #N/A response. Could the data type be an issue? They are general fields
right now, but I could change them to number or text fields? Would that work?
I am getting a little frustrated at this point. Thanks so much for all of you
help.

"Dave Peterson" wrote:

I'd just use column M in the index and drop the 13, but that's not the problem:

=INDEX(Vlookup!$M$1578:$M$4802,
MATCH(1,(B332=Vlookup!$A$1578:$A$4802)
*(LEFT(F332,6)=Vlookup!$D$1578:$D$4802),0))

Still array entered, too.




Lost in Microbiology wrote:

Dave:

This is the formula I inputed:
=INDEX(Vlookup!$A$1578:$M$4802,MATCH(B332,Vlookup! $A$1578:$A$4802,0)*MATCH(LEFT(F332,6),Vlookup!$D$1 578:$D$4802,0),13)

I did hit ctrl-shift-enter and got the brackets correctly. However the
result was a #N/A. When I went to check, there should have been a result. Any
thoughts on what I did wrong?

Thanks.

"Dave Peterson" wrote:

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:

I have a list (~1500 entries) that I need to match patients with their
specimen. The problem comes where a single patient will have multiple
entries. Then it only finds the first occurrence and not a subsequent sample.

Is there an if then statement or a way to use 2 columns for criteria in a
vlookup? I want to match a patient medical record number and a received date
tofind the result.

Here is an example of my data:
Name MRN Accession Rec Date Final Result
John Doe 12345 07-000-0001 1/1/2007 No Virus
John Doe 12345 07-020-0000 1/20/2007 RSV
Jane Doe 3456 07-000-0002 1/1/2007 No Virus

I have 2 lists like this, one for one procedure and another for another
procedure, and I have to match the information to determine specificity of
the tests. Any help is greatly appreciated!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

If you have lots of columns and want to select records based upon criteria in
several of the columns, strongly consider using AutoFilter. This technique
will allow you to pick criteria on some or all of the columns and find only
the records that match all the criteria.

see:

http://www.contextures.com/xlautofilter01.html
--
Gary''s Student - gsnu200764


"Lost in Microbiology" wrote:

I have a list (~1500 entries) that I need to match patients with their
specimen. The problem comes where a single patient will have multiple
entries. Then it only finds the first occurrence and not a subsequent sample.

Is there an if then statement or a way to use 2 columns for criteria in a
vlookup? I want to match a patient medical record number and a received date
tofind the result.

Here is an example of my data:
Name MRN Accession Rec Date Final Result
John Doe 12345 07-000-0001 1/1/2007 No Virus
John Doe 12345 07-020-0000 1/20/2007 RSV
Jane Doe 3456 07-000-0002 1/1/2007 No Virus

I have 2 lists like this, one for one procedure and another for another
procedure, and I have to match the information to determine specificity of
the tests. Any help is greatly appreciated!