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" wrote:

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