vlookup:same lookup_value, different returns

Hi!

In general, there are better ways to write this formula:

=If(A1=VLOOKUP(A1,'SHEET'!A$1:D$100,1,FALSE),VLOOKUP(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),INDEX(Sheet1!C$1:C$100,SMALL(IF(Sheet1!A$1:A$100=A$1,ROW(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" <[email protected]> wrote in
message news:[email protected]...
>
> I have something like this:
>
> =If(A1=VLOOKUP(A1,'SHEET'!A$1:D$100,1,FALSE),VLOOKUP(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
>

Thank you Biff! I'll check it out.