Returning Multiple values from A Vlookup

I need a formula to return multiple values from a look up table as the number 
appears multiple times in a column.  
Reference cell is $C$6 eg = 4101

Current formula only return the first one it finds 
=IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE)))

I need the Cell to return all the values it finds from the one code eg  = 
QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW

Lookup table is TNI (A1:C1053) I need it to return all values in Column (C)
   A	              	B			        C
4077	All suburbs	        QRLE/QRLD 
4078	Forest Lake	        QRLD/QLGH 
4101	Highgate Hill	        QCBD 
4101	South Brisbane	QTNS/QCBD/QBMH/QMRE 
4101	West End	                QCBD/QAGW 
4102	Dutton Park	        QCBD
4102	Woolloongabba	QTNS/QBMH/QMRE/QBBS
4103	All suburbs	        QTNS 

Thanks
Michael 

0
Utf
2/22/2010 7:01:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
2525 Views

Similar Articles

[PageSpeed] 3

Perhaps consider using Autofilter?

Sharon

"mickn74" wrote:

> I need a formula to return multiple values from a look up table as the number 
> appears multiple times in a column.  
> Reference cell is $C$6 eg = 4101
> 
> Current formula only return the first one it finds 
> =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE)))
> 
> I need the Cell to return all the values it finds from the one code eg  = 
> QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW
> 
> Lookup table is TNI (A1:C1053) I need it to return all values in Column (C)
>    A	              	B			        C
> 4077	All suburbs	        QRLE/QRLD 
> 4078	Forest Lake	        QRLD/QLGH 
> 4101	Highgate Hill	        QCBD 
> 4101	South Brisbane	QTNS/QCBD/QBMH/QMRE 
> 4101	West End	                QCBD/QAGW 
> 4102	Dutton Park	        QCBD
> 4102	Woolloongabba	QTNS/QBMH/QMRE/QBBS
> 4103	All suburbs	        QTNS 
> 
> Thanks
> Michael 
> 
0
Utf
2/22/2010 8:55:01 AM
Please note that this is an array formula. You create array formulas in the 
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
enter the formula. If successful in 'Formula Bar' you can notice the curly 
braces at both ends like "{=<formula>}"

=IF(COUNTIF(INDEX(TNI,,1),$C$6)<ROW(A1),"",
INDEX(TNI,SMALL(IF(INDEX(TNI,,1)=$C$6,
ROW(INDEX(TNI,,1))),ROW(A1)),3))

-- 
Jacob


"mickn74" wrote:

> I need a formula to return multiple values from a look up table as the number 
> appears multiple times in a column.  
> Reference cell is $C$6 eg = 4101
> 
> Current formula only return the first one it finds 
> =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE)))
> 
> I need the Cell to return all the values it finds from the one code eg  = 
> QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW
> 
> Lookup table is TNI (A1:C1053) I need it to return all values in Column (C)
>    A	              	B			        C
> 4077	All suburbs	        QRLE/QRLD 
> 4078	Forest Lake	        QRLD/QLGH 
> 4101	Highgate Hill	        QCBD 
> 4101	South Brisbane	QTNS/QCBD/QBMH/QMRE 
> 4101	West End	                QCBD/QAGW 
> 4102	Dutton Park	        QCBD
> 4102	Woolloongabba	QTNS/QBMH/QMRE/QBBS
> 4103	All suburbs	        QTNS 
> 
> Thanks
> Michael 
> 
0
Utf
2/22/2010 9:23:01 AM
Hi,

You may refer to my solution here - 
http://office.microsoft.com/en-us/excel/HA012260381033.aspx

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mickn74" <mickn74@discussions.microsoft.com> wrote in message 
news:5824E1AE-1553-4B81-9640-C7B60B6B9C82@microsoft.com...
> I need a formula to return multiple values from a look up table as the 
> number
> appears multiple times in a column.
> Reference cell is $C$6 eg = 4101
>
> Current formula only return the first one it finds
> =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE)))
>
> I need the Cell to return all the values it finds from the one code eg  =
> QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW
>
> Lookup table is TNI (A1:C1053) I need it to return all values in Column 
> (C)
>   A               B         C
> 4077 All suburbs         QRLE/QRLD
> 4078 Forest Lake         QRLD/QLGH
> 4101 Highgate Hill         QCBD
> 4101 South Brisbane QTNS/QCBD/QBMH/QMRE
> 4101 West End                 QCBD/QAGW
> 4102 Dutton Park         QCBD
> 4102 Woolloongabba QTNS/QBMH/QMRE/QBBS
> 4103 All suburbs         QTNS
>
> Thanks
> Michael
> 
0
Ashish
2/27/2010 4:13:21 AM
Reply:

Similar Artilces:

Multiple user names at one bank or brokerage
I have 3 accounts at one bank. I have 3 corresponding accounts set up in MS Money 2007. One of them is setup for online banking while the other two are not. I now want to set the other two up for online banking but I can't find a way to specify the unique login info for each of the other two accounts. ...

how do i change the default value of measure from points to inche.
how do i change the default value of measure from points to inches when setting the width and hight of cells? You don't. Excel uses only points for these measures best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "yoyo4u" <yoyo4u@discussions.microsoft.com> wrote in message news:33420157-6E05-4A55-9003-088D731E495E@microsoft.com... > how do i change the default value of measure from points to inches when > setting the width and hight of cells? yoyo Row heights are measured in points. There are 72 points to an inch. Th...

Excel web query returns no data
When I enter a web addreess into IE it shows the page When I set up a web query in Excel it allows me to select the relevant tables, but then returns no data The same Excel query worked on previuous versions of Excel Web address for query http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBM DataServlet?param1=T_CNQPS- 4&param2=&param3=&param4=&param5=2001-04-01&param6=* Any help appreciated ...

Multiple Domain e-mail addresses
We are running Exchange Server 2003 Enterprise addition and I am having a bit of a "brain-freeze" at the moment... Where in the Exchange Admin do I configure the server to accept e-mail from more than one domain. For example, we currently accept e-mail from mycurrentdomain.ca and would also like to accept email from mycurrentdomain.com. Please note that I have updated all of our DNS MX records as required, just need to make the changes on the Exchange server. Thanks in advance... Recipient polices-email address policy. "Raymond Spitzingle" <ray(nospam)@stuff...

Chart to show Portfolio Value over time?
I'm using Money 2003. I would like to be able to see the $ value I have in my portfolio over time. So that I can weep. Money does have a chart view that allows you to see the PRICE history for a given stock over time - but not the dollar value of your investment in the stock over time. In fact, I can't seem to find a view at all that shows you the net value of your portfolio/individual stocks changing over time. The best I've been able to do is to use the "Net Worth" report and unselect all the other accounts. This has insufficient granularity (months instea...

Amazing Problem in AllocPhysMem wince 6.0
Hi I am writing a Port Driver for x86 platform in WinCE 6.0. I want to allocate virtual and equivalent physical memory in driver and mapped it to USER mode to use application. For that I used AllocPhysMem in driver and passed that address through IOCTL calls but i cant use that virtual and physical address in application side. Because AllocPhysMem returns Error Code as 0x57 (meaning Parameter incorrect). But the same code is working in WinCe 5.0. My code snippet is, VirAddress = (LPVOID)AllocPhysMem(32, PAGE_READWRITE|PAGE_NOCACHE, 0, ...

Insert empty numeric value
Dear all, In VB, I have three textbox which are amount1,amount2 and amount3. After user enter the value in the textbox, I will insert the value into Access table. The table have three columns amount1 , amount2 and amount3, and all are nummeric Type. However, if the user do not enter any value in textbox . The insert statement will become as follows: Insert into table1 (amount1,amount2,amount3) values (,,) Then access complain that there is syntax error in insert statement. Does that mean I cannot insert empty value for the numeric value in access.? How to solve this problem. Than...

Multiple accounts #3
I have 2 Hotmail and 1 MSN accounts set up in Outlook 2002. It shows that it sends and receives all 3 accounts successfully, but when I go to the Inbox of the Hotmail accounts, there aren't any messages. Thanks, Rick It says it send and received fine, but problems occur when you set up more than one HTTP account, so it is recommended that you only use one per profile ...

HOWTO create a Mail Merge Template in MSCRM with multiple child records
Hi, I have created successfully a basic Mail Merge template in MS Word in Dynamics CRM 4.0 for Order Entity. My challenge now is how to design a template that retrieves the order details (product and prices) related to the order record? This sounds like a parent-child template. Any idea? On Jun 2, 1:26=A0pm, "Benjie Fallar III" <bfall...@hotmail.com> wrote: > Hi, > I have created successfully a basic Mail Merge template in MS Word in > Dynamics CRM 4.0 for Order Entity. > My challenge now is how to design a template that retrieves the order > details (prod...

Outlook/Access 2003
I've been doing a lot of research and purchased (but haven't completely read) Sue Mosher's book. However, before I spend much more time I'd like to find out if my idea is even feasible. The basic idea is to create an appointment system for multiple therapists that can be used by a receptionist and other staff. Using Outlook and Exchange is very nice but the appointment data needs to end up in an Access table immediately. We can purchase SBS 2003 with Exchange 2003 or we can buy a third party tool for calendar sharing whichever would be the easiest to use. I currently...

conditional sum returns not expected
Hi all. Using xl xp pro Here is conditional sum formula =SUM(IF(FAR_All_Data!$B$2:$B$1622=C$2,IF(FAR_All_Data!$B$2:$B$1622=C$3,IF(FAR_All_Data!$A$2:$A$1622=$A5,FAR_All_Data!$D$2:$D$1622,0),0),0)) Sorry about the wrapping. Where C$2 = 7001.4500 Where C$3 = 7001.4501 Where $A5 = 54608001 Where Far_All_Data! contains data, all ref's verified However, all formulas return 0.00. As I copy down the formula Col (54608001) udates ...002, 003, 004, etc... Sometimes the expected result is 0.00 other times value should b greater than 0 Not sure how to read the formula. Does it say if, or, and? ...

can lookup return cell reference istead of "text" for sumif?
I am trying to use a lookup-function to determine a different sum range for several criteria. Like so: =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;false)-Sumif($A$7:$A $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false) The problem is that the vlookup returns text and not the cell reference. Is there a way to get the answer from the lookup expressed as cell reference instead of text, since sumif can't use text, just the cell reference? I use it to calculate the number of hours the staff should be paid, so it's different from weekdays to saturdays, holidays...

Replace null value with the previous value?
I have a database that was just imported that has approximately 388000 records. The problem is that there is information about a person in multiple different records but the name did not come across with each record. (So I have 10 records with information for a certain name, but the name only appears in field 1 of the first record and not the subsequent 9, etc.) I need to create a query or expression that will fill field 1 with the preceding value if it is null. This way I will have all the information for field 1 in a manner that I can link and combine data. Simply I need to fil...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

How do I find a value on a line?
I made a line graph of data to use as a calibration. I know the y value and I want to find the X value. Is there a way that I find find this specifically on the line without using a trendline formula or guessing by looking at the gridlines? The only way to find a specific value is to use the formula. Rgs, Bou If you can accept piece-wise linear interpolation, see Interactive Chart http://www.tushar- mehta.com/excel/software/interactive_chart_display/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity s...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

Subtracting value from main form
I have a borrow module which will alow user to return item separately. So, I have get the structure of returning it separately. In my main form is the borrowing item, with the loaned quantity and the owed quantity (will be calculated). In the subform, there is the returning transaction. User will need to key in the quantity returned and it will be automatically deducted from the quantity owed. But how am I supposed to get the quantity deducted while it 1 is in main form and the other is in subform? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-fo...

Out of Office Assistant replies multiple times
The situation: The user of a fully patched Outlook 2003 SP1 sets an Out of Office reply using the Out Of Office Assistant.. The result: The first time someone sends a message while the OOA reply is turned on, they get the full text of the OOA reply as it is configured. The second and subsequent emails from that same sender gets a truncated version of the OOA message. In my testing, my second message (which isn't supposed to be replied to), got me 2 truncated replies. Examples from my testing: 1st reply: "I am out of the office and will return on Monday, February 28, 2005. At th...

vlookup help #3
hello, I cant figure out the proper syntax for the attached workbook. Th first 8 options on worksheet two work perfectly ( ie when checked, the appear on worksheet 3) I dont know how to apply the formula of th first 8 options to the rest of the checkboxes. Can someone please help me figure out how to do this? Thanks i advance. Aladdi Attachment filename: start order2.zip Download attachment: http://www.excelforum.com/attachment.php?postid=46674 -- Message posted from http://www.ExcelForum.com Right-click on the sheet tab for the Options worksheet. Select View ...

Can i move multiple lines between workbooks
I have a .csv format file listing speed cameras, and the different speeds are identified by two characters at the end of each line - 30, 40, 50 etc. I would like to create separate files for each speed, but Excel won't allow me to cut/copy several lines to created a new file/workbook. Is there any way round this? Martin Ellis Sun, 16 Sep 2007 03:28:00 -0700 from Martin Ellis <MartinEllis@discussions.microsoft.com>: > I have a .csv format file listing speed cameras, and the different speeds are > identified by two characters at the end of each line - 30, 40, 50 etc...

Vlookup, multiple times in one column summing corresponding
I have a list that contains common invoice numbers that appear multipl times in column A and need to use the vlookup function to find and su the corresponding amounts in column B. As you would be aware the following vlookup functio =VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i reaches a match and I need it to continue down the column and sum al matching invoice numbers. Column A Column B 145768 356.87 145769 678.90 145880 80.87 * 145769 103.55 145770 56.90 145769 78.32 145880 54.09 * The answer I�m looking to return is 145880 134.96 It has to be a vlookup sty...

IF Multiple Conditions
A B C D E Color Total Time Total Time 2 Time Allowed Y/NO BLUE 1:22:33 2:22:33 1:00:00 BLUE 2:22:33 3:22:33 2:00:00 BLUE 3:22:33 4:22:33 3:00:00 BLUE 4:22:33 5:22:33 4:00:00 BLUE 5:22:33 6:22:33 5:00:00 RED 6:22:33 7:22:33 6:00:00 RED 7:22:33 8:22:33 7:00:00 RED 8:22:33 9:22:33 8:00:00 I need an IF formula that: - will read IF condition in the A column is Blue it will bring back...

One front-end server multiple back-end servers
Hi, I was wondering if someone could help with the following scenerio and make some suggestions as to an answer. We have 3 exchange AG groups. 1 AG group has 3 exchange servers in it, each located in it's own AD domain. 1 AG group has one exchange server in it, it's in its own AD domain. Thirdly, the last AG group has 2 exchange servers in it, both are in the same AD domain. 1 of these 2 exchange servers in the final AG mentioned is a front-end server, that should be supporting all the exchange back-end servers. The problem we are facing is that when a user from any of the ...

multiplication in an Excell Spreadsheet
I am having difficulty in multiplying 285 units by 5.960 p. per unit. Can anyone help me solve this problem please? With 285 in cell A1 and 5.96 in cell A2, in cell A3 put =A1*A2 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "KiaOra" <KiaOra@discussions.microsoft.com...