2 Conditions in VLookup

[Tina Hudson]

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)),Reports!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),E1:E1000))

Any help will be most appreciated.

 

[Jacob Skaria]

Try the below (sheet reference was missing)

=IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E1000)),0,LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E1000))

If this post helps click Yes

 

[Tina Hudson]

Woo Hoo!!! It worked!

Thanks a million!

 

[Tina Hudson]

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?

 

[Jacob Skaria]

=IF(ISERROR(LOOKUP(2,1/((Reports!A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E1000)),"",LOOKUP(2,1/((A1:A1000=A3)*(Reports!B1:B1000=B3)),Reports!E1:E1000))

If this post helps click Yes

 

[Tina Hudson]

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

 

[Ashish Mathur]

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