Multiple returns with one lookup value
 

The typical answer to a vlookup trying to return multiple values.
List has 3 lookup values, and vlookup returns first only.

So INDEX SMALL formula is the answer.

I have the suggested formula but not quite correctly done. So, this is for an example sheet I can look at as a syntax guide.

Want to pull across three columns to display the three returns for a horizontal display.

Would like to pull down three cell for a vertical display.

G2 has a drop down of the lookup values.

https://www.dropbox.com/s/e53bgn8r1m...urns.xlsm?dl=0

Thanks

Howard

Multiple returns with one lookup value
 

Hi Howard,

Am Fri, 24 Apr 2015 22:17:36 -0700 (PDT) schrieb L. Howard:

So INDEX SMALL formula is the answer.

you copy the formula to the right so you have to use COLUMN for SMALL.

In I2:
=IFERROR(INDEX(Second!$B:$B,SMALL(IF(Second!$A$1:$ A$50=$G2,ROW($A$1:$A$50)),COLUMN(A1))),"")
and insert the formula with CTRL+Shift+Enter
and copy to the right


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Multiple returns with one lookup value
 

In I2:
=IFERROR(INDEX(Second!$B:$B,SMALL(IF(Second!$A$1:$ A$50=$G2,ROW($A$1:$A$50)),COLUMN(A1))),"")
and insert the formula with CTRL+Shift+Enter
and copy to the right


Regards
Claus B.
--

Aha! Got it.

And the $G2 to pull to the right.
And the G$2 to pull down.

Thanks Claus.