vlookup 2 criteria

[Lost in Microbiology]

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]

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))

 

[Gary''s Student]

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

 

[Lost in Microbiology]

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$1578:$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]

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]

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]

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?

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.

 

[Lost in Microbiology]

Both of the fields have text in them. I will change them to numberic fields
and see if that helps. B332 looks like this: (0000)123456 and F332 looks
like: 04-001-1234
Let me know if that would be the problem.

 

[Dave Peterson]

I'm not sure if that's the problem.

Each of those cells can contain numbers that are formatted to look like what you
typed.

If the values are really numbers (just formatted to look that way), then the
table has to have real numbers, too.

If the values are really text, then the table has to have real text.

Pick out a row that should match (say 2222).
The put these formulas in empty cells:
=B332=Vlookup!A2222
and
=left(f332,6)=vlookup!d2222

If you get true in both formulas, then the larger formula should work ok. If it
doesn't, please post the current formula you're using. Just to make sure you
haven't changed something important.

Both of the fields have text in them. I will change them to numberic fields
and see if that helps. B332 looks like this: (0000)123456 and F332 looks
like: 04-001-1234
Let me know if that would be the problem.

 

[Lost in Microbiology]

REALLY stupid error. As I was going through the formula step by step, I
realized the cell in F wasn't being highlighted. It was because it was really
column J, there were some unneeded data that had been hidden, and so just
counting over the number of columns was giving me the wrong cell. Thanks so
much, this has made going over this and reports like it so much quicker and
efficient!

Have a great day!

 

[Dave Peterson]

Whew! I was running out of guesses!

REALLY stupid error. As I was going through the formula step by step, I
realized the cell in F wasn't being highlighted. It was because it was really
column J, there were some unneeded data that had been hidden, and so just
counting over the number of columns was giving me the wrong cell. Thanks so
much, this has made going over this and reports like it so much quicker and
efficient!

Have a great day!