|
2 Conditions in VLookup
Good monrning,
Biff has been helping me with this, but I wasn't sure if I should add to previous post since it's something slightly different. So, I apologize if I did wrong. I've looked at previous posts but still can't get it to work. I have 2 worksheets in 1 spreadsheet. The first worksheet, Reports, contains the following: A B E Case Name Report Date Worker Jones, Thaddeus 9/1/2009 Dilbert Smith, Thomas 8/23/2009 Giles Jones, Thaddeus 9/15/2009 Dilbert Smith, Mary 9/14/2009 Jenkins The second worksheet, Treatment, contains the following: A B C Case Name Report Date Worker I want to be able to type in the Case Name and Report date in the second worksheet (because I can have duplicate case name, but not duplicate report date) and have the appropriate worker automatically appear like magic in column C. (I have other data too that I want copied over, but that's really irrelevant here!) I've tried typing in the following formula in Treatment!C2, but am getting #N/A: =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),E1:E1000)) Any help will be most appreciated. -- Thanks, Tina Hudson |
2 Conditions in VLookup
Try the below (sheet reference was missing)
=IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E 1000)) If this post helps click Yes --------------- Jacob Skaria "Tina Hudson" wrote: Good monrning, Biff has been helping me with this, but I wasn't sure if I should add to previous post since it's something slightly different. So, I apologize if I did wrong. I've looked at previous posts but still can't get it to work. I have 2 worksheets in 1 spreadsheet. The first worksheet, Reports, contains the following: A B E Case Name Report Date Worker Jones, Thaddeus 9/1/2009 Dilbert Smith, Thomas 8/23/2009 Giles Jones, Thaddeus 9/15/2009 Dilbert Smith, Mary 9/14/2009 Jenkins The second worksheet, Treatment, contains the following: A B C Case Name Report Date Worker I want to be able to type in the Case Name and Report date in the second worksheet (because I can have duplicate case name, but not duplicate report date) and have the appropriate worker automatically appear like magic in column C. (I have other data too that I want copied over, but that's really irrelevant here!) I've tried typing in the following formula in Treatment!C2, but am getting #N/A: =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),E1:E1000)) Any help will be most appreciated. -- Thanks, Tina Hudson |
2 Conditions in VLookup
Woo Hoo!!! It worked!
Thanks a million! -- Thanks, Tina Hudson "Jacob Skaria" wrote: Try the below (sheet reference was missing) =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E 1000)) If this post helps click Yes --------------- Jacob Skaria "Tina Hudson" wrote: Good monrning, Biff has been helping me with this, but I wasn't sure if I should add to previous post since it's something slightly different. So, I apologize if I did wrong. I've looked at previous posts but still can't get it to work. I have 2 worksheets in 1 spreadsheet. The first worksheet, Reports, contains the following: A B E Case Name Report Date Worker Jones, Thaddeus 9/1/2009 Dilbert Smith, Thomas 8/23/2009 Giles Jones, Thaddeus 9/15/2009 Dilbert Smith, Mary 9/14/2009 Jenkins The second worksheet, Treatment, contains the following: A B C Case Name Report Date Worker I want to be able to type in the Case Name and Report date in the second worksheet (because I can have duplicate case name, but not duplicate report date) and have the appropriate worker automatically appear like magic in column C. (I have other data too that I want copied over, but that's really irrelevant here!) I've tried typing in the following formula in Treatment!C2, but am getting #N/A: =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),E1:E1000)) Any help will be most appreciated. -- Thanks, Tina Hudson |
2 Conditions in VLookup
Jacob,
If I wanted to get rid of the 0 (zero) that comes up if either the Case Name or Report Date (or both) is blank, how would I add that to the code? -- Thanks, Tina Hudson "Jacob Skaria" wrote: Try the below (sheet reference was missing) =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E 1000)) If this post helps click Yes --------------- Jacob Skaria "Tina Hudson" wrote: Good monrning, Biff has been helping me with this, but I wasn't sure if I should add to previous post since it's something slightly different. So, I apologize if I did wrong. I've looked at previous posts but still can't get it to work. I have 2 worksheets in 1 spreadsheet. The first worksheet, Reports, contains the following: A B E Case Name Report Date Worker Jones, Thaddeus 9/1/2009 Dilbert Smith, Thomas 8/23/2009 Giles Jones, Thaddeus 9/15/2009 Dilbert Smith, Mary 9/14/2009 Jenkins The second worksheet, Treatment, contains the following: A B C Case Name Report Date Worker I want to be able to type in the Case Name and Report date in the second worksheet (because I can have duplicate case name, but not duplicate report date) and have the appropriate worker automatically appear like magic in column C. (I have other data too that I want copied over, but that's really irrelevant here!) I've tried typing in the following formula in Treatment!C2, but am getting #N/A: =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),E1:E1000)) Any help will be most appreciated. -- Thanks, Tina Hudson |
2 Conditions in VLookup
=IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),"",LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E 1000))
If this post helps click Yes --------------- Jacob Skaria "Tina Hudson" wrote: Jacob, If I wanted to get rid of the 0 (zero) that comes up if either the Case Name or Report Date (or both) is blank, how would I add that to the code? -- Thanks, Tina Hudson "Jacob Skaria" wrote: Try the below (sheet reference was missing) =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E 1000)) If this post helps click Yes --------------- Jacob Skaria "Tina Hudson" wrote: Good monrning, Biff has been helping me with this, but I wasn't sure if I should add to previous post since it's something slightly different. So, I apologize if I did wrong. I've looked at previous posts but still can't get it to work. I have 2 worksheets in 1 spreadsheet. The first worksheet, Reports, contains the following: A B E Case Name Report Date Worker Jones, Thaddeus 9/1/2009 Dilbert Smith, Thomas 8/23/2009 Giles Jones, Thaddeus 9/15/2009 Dilbert Smith, Mary 9/14/2009 Jenkins The second worksheet, Treatment, contains the following: A B C Case Name Report Date Worker I want to be able to type in the Case Name and Report date in the second worksheet (because I can have duplicate case name, but not duplicate report date) and have the appropriate worker automatically appear like magic in column C. (I have other data too that I want copied over, but that's really irrelevant here!) I've tried typing in the following formula in Treatment!C2, but am getting #N/A: =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),E1:E1000)) Any help will be most appreciated. -- Thanks, Tina Hudson |
2 Conditions in VLookup
Jacob,
Thanks. I still get a 0 with that code. I had posted yesterday with the same kind of problem (only with 1 condition with VLookup) and so I applied the response to my previous question with this issue and it worked. Here is the final code for those with similar questions: =IFERROR(IF(LOOKUP(2,1/((Reports!$A$1:$A$1000=A13)*(Reports!$B$1:$B$1000= B13)),Reports!$F$1:$F$1000)=0,"",LOOKUP(2,1/((Reports!$A$1:$A$1000=A13)*(Reports!$B$1:$B$1000= B13)),Reports!$F$1:$F$1000)),"") As you can see, I added IFERROR with nested IF. Again, thanks! Tina Hudson "Jacob Skaria" wrote: =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),"",LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E 1000)) If this post helps click Yes --------------- Jacob Skaria "Tina Hudson" wrote: Jacob, If I wanted to get rid of the 0 (zero) that comes up if either the Case Name or Report Date (or both) is blank, how would I add that to the code? -- Thanks, Tina Hudson "Jacob Skaria" wrote: Try the below (sheet reference was missing) =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E 1000)) If this post helps click Yes --------------- Jacob Skaria "Tina Hudson" wrote: Good monrning, Biff has been helping me with this, but I wasn't sure if I should add to previous post since it's something slightly different. So, I apologize if I did wrong. I've looked at previous posts but still can't get it to work. I have 2 worksheets in 1 spreadsheet. The first worksheet, Reports, contains the following: A B E Case Name Report Date Worker Jones, Thaddeus 9/1/2009 Dilbert Smith, Thomas 8/23/2009 Giles Jones, Thaddeus 9/15/2009 Dilbert Smith, Mary 9/14/2009 Jenkins The second worksheet, Treatment, contains the following: A B C Case Name Report Date Worker I want to be able to type in the Case Name and Report date in the second worksheet (because I can have duplicate case name, but not duplicate report date) and have the appropriate worker automatically appear like magic in column C. (I have other data too that I want copied over, but that's really irrelevant here!) I've tried typing in the following formula in Treatment!C2, but am getting #N/A: =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),E1:E1000)) Any help will be most appreciated. -- Thanks, Tina Hudson |
2 Conditions in VLookup
Hi,
You may array enter the following (Ctrl+Shift+Enter) =INDEX(Reports!$A$2:$C$6,MATCH(1,(Reports!$A$2:$A$ 6=Treatment!B5)*(Reports!B2:B6=Treatment!C5),0),3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Tina Hudson" wrote in message ... Good monrning, Biff has been helping me with this, but I wasn't sure if I should add to previous post since it's something slightly different. So, I apologize if I did wrong. I've looked at previous posts but still can't get it to work. I have 2 worksheets in 1 spreadsheet. The first worksheet, Reports, contains the following: A B E Case Name Report Date Worker Jones, Thaddeus 9/1/2009 Dilbert Smith, Thomas 8/23/2009 Giles Jones, Thaddeus 9/15/2009 Dilbert Smith, Mary 9/14/2009 Jenkins The second worksheet, Treatment, contains the following: A B C Case Name Report Date Worker I want to be able to type in the Case Name and Report date in the second worksheet (because I can have duplicate case name, but not duplicate report date) and have the appropriate worker automatically appear like magic in column C. (I have other data too that I want copied over, but that's really irrelevant here!) I've tried typing in the following formula in Treatment!C2, but am getting #N/A: =IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Repo rts!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),E1:E1000)) Any help will be most appreciated. -- Thanks, Tina Hudson |
| All times are GMT +1. The time now is 03:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com