Hi!
In general, there are better ways to write this formula:
=If(A1=VLOOKUP(A1,'SHEET'!A$1:D$100,1,FALSE),VLOOK UP(A1,'SHEET'!A$1:G$100,*3*,FALSE),"")
Try one of these:
=IF(ISNA(MATCH(A1,Sheet1!A$1:A$100,0)),"",VLOOKUP( A1,Sheet1!A$1:G$100,3,0))
=IF(COUNTIF(Sheet1!A$1:A$100,A1),VLOOKUP(A1,Sheet1 !A$1:G$100,3,0),"")
If there are multiple instances of the lookup value use this formula entered
as an array using the key combo of CTRL,SHIFT,ENTER:
=IF(ROWS($1:1)<=COUNTIF(Sheet1!A$1:A$100,A$1),INDE X(Sheet1!C$1:C$100,SMALL(IF(Sheet1!A$1:A$100=A$1,R OW(A$1:A$100)-ROW(A$1)+1),ROWS($1:1))),"")
Copy down until you bet blanks.
BTW :is it redundant to use the condition and then use a FALSE range
lookup?
Not sure what you mean by that ???
Biff
"javino" wrote in
message ...
I have something like this:
=If(A1=VLOOKUP(A1,'SHEET'!A$1:D$100,1,FALSE),VLOOK UP(A1,'SHEET'!A$1:G$100,*3*,FALSE),"")
If the condition is true more than once, how can it return the
additional corresponding values of column 3?
BTW :is it redundant to use the condition and then use a FALSE range
lookup?
--
javino
------------------------------------------------------------------------
javino's Profile:
http://www.excelforum.com/member.php...o&userid=29678
View this thread: http://www.excelforum.com/showthread...hreadid=493983