Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 25th 15, 06:17 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 852
Default 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


  #2   Report Post  
Old April 25th 15, 06:52 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,866
Default 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
  #3   Report Post  
Old April 25th 15, 07:42 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 852
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vertical lookup with a lookup value that returns multiple matches andreashermle Excel Programming 3 June 15th 10 12:39 AM
Lookup with multiple returns Pirate Excel Discussion (Misc queries) 12 May 7th 09 07:03 PM
lookup with multiple returns Jay Excel Worksheet Functions 8 October 2nd 08 06:10 PM
Lookup which returns multiple values which are additive ExcelMonkey Excel Worksheet Functions 1 January 3rd 07 08:52 PM
Lookup table returns #N/A Kimberly Bassininsky Excel Discussion (Misc queries) 2 February 2nd 06 07:31 PM


All times are GMT +1. The time now is 11:08 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017