vlookup:same lookup_value, different returns

[Biff]

Hi!

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

=If(A1=VLOOKUP(A1,'SHEET'!A$1$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]

I have something like this:

=If(A1=VLOOKUP(A1,'SHEET'!A$1$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]

Thank you Biff! I'll check it out.