Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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. Lost in Microbiology wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
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" wrote: 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. Lost in Microbiology wrote: 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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup 2 criteria
Whew! I was running out of guesses!
Lost in Microbiology wrote: 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" wrote: 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. Lost in Microbiology wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using vlookup for more than 1 criteria | Excel Worksheet Functions | |||
Vlookup with more than 1 criteria | Excel Discussion (Misc queries) | |||
vlookup with two criteria | Excel Worksheet Functions | |||
vlookup with two criteria | Excel Worksheet Functions | |||
two criteria in a vlookup | Excel Worksheet Functions |