Home 
Search 
Today's Posts 
#1




how do i get mutiple values using vlookup in excel, lookup value .
hi , i have data stored in excel as column a Purchase order no column b
Invoice no i want to query basis purchase order no & result should give mutiple invoice no stored agst one purchase order no how do i do this using lookup or something ! 
#2




I use the following for something similar. It might be what you are looking
for:  =IF(ISERROR(INDEX(B1:B12500,SMALL(IF(A1:A12500=D1, ROW(B1:B12500),""),ROW()))=FALSE),"",(INDEX(B1:B12 500,SMALL(IF(A1:A12500=D1,ROW(B1:B12500),""),ROW() )))) Enter as Array Formula CTRL+SHIFT+ENTER Copy this down as many rows as you think there are unique numbers (say 100 rows) Column A would be your Invoice Numbers Column B would be your Purchase order Numbers D1 would be the Purchase order number you are searching for "Abhijeet" wrote: hi , i have data stored in excel as column a Purchase order no column b Invoice no i want to query basis purchase order no & result should give mutiple invoice no stored agst one purchase order no how do i do this using lookup or something ! 
#3




How about a User Defined Function?
This returns a list separated by commas to a single cell. http://groups.google.co.uk/groups?th...apeXSPAM.co m Abhijeet wrote: hi , i have data stored in excel as column a Purchase order no column b Invoice no i want to query basis purchase order no & result should give mutiple invoice no stored agst one purchase order no how do i do this using lookup or something !  Dave Peterson 
#4




Anthony,
I tried to use your formula for my application but it is not working what am I missing? The formula I am using is: =IF(ISERROR(INDEX(Data!$E$1:$E$16200,SMALL(IF(Data !$H$1:$H$16200=$E$5&$B11,ROW(Data!$E$1:$E$16200)," "),ROW()))=FALSE),"",(INDEX(Data!$E$1:$E$16200,SMA LL(IF(Data!$H$1:$H$16200=$E$5&$B11,ROW(Data!$E$1:$ E$16200),""),ROW())))) $E$5&$B11= The JOb#_Phase Type I am looking for. Data!$E$1:$E$16 = The range where the Job#_Phase Type are found multiple times. Data!$H$1:$H$16200=The range where the dates are found or the answers I want returned. I also tried: =IF(ISERROR(INDEX(Data!$H$1:$H$16200,SMALL(IF(Data !$E$1:$E$16200=$E$5&$B11,ROW(Data!$H$1:$H$16200)," "),ROW()))=FALSE),"",(INDEX(Data!$H$1:$H$16200,SMA LL(IF(Data!$E$1:$E$16200=$E$5&$B11,ROW(Data!$H$1:$ H$16200),""),ROW())))) Both Instances the Cell is blank.  Thanks, Ben "Anthony Slater" wrote: I use the following for something similar. It might be what you are looking for:  =IF(ISERROR(INDEX(B1:B12500,SMALL(IF(A1:A12500=D1, ROW(B1:B12500),""),ROW()))=FALSE),"",(INDEX(B1:B12 500,SMALL(IF(A1:A12500=D1,ROW(B1:B12500),""),ROW() )))) Enter as Array Formula CTRL+SHIFT+ENTER Copy this down as many rows as you think there are unique numbers (say 100 rows) Column A would be your Invoice Numbers Column B would be your Purchase order Numbers D1 would be the Purchase order number you are searching for "Abhijeet" wrote: hi , i have data stored in excel as column a Purchase order no column b Invoice no i want to query basis purchase order no & result should give mutiple invoice no stored agst one purchase order no how do i do this using lookup or something ! 
#5




Hi!
This (these) formulas are dependant upon which row they're in: ......ROW())))) See my reply to your other post. Biff "Ben" wrote in message ... Anthony, I tried to use your formula for my application but it is not working what am I missing? The formula I am using is: =IF(ISERROR(INDEX(Data!$E$1:$E$16200,SMALL(IF(Data !$H$1:$H$16200=$E$5&$B11,ROW(Data!$E$1:$E$16200)," "),ROW()))=FALSE),"",(INDEX(Data!$E$1:$E$16200,SMA LL(IF(Data!$H$1:$H$16200=$E$5&$B11,ROW(Data!$E$1:$ E$16200),""),ROW())))) $E$5&$B11= The JOb#_Phase Type I am looking for. Data!$E$1:$E$16 = The range where the Job#_Phase Type are found multiple times. Data!$H$1:$H$16200=The range where the dates are found or the answers I want returned. I also tried: =IF(ISERROR(INDEX(Data!$H$1:$H$16200,SMALL(IF(Data !$E$1:$E$16200=$E$5&$B11,ROW(Data!$H$1:$H$16200)," "),ROW()))=FALSE),"",(INDEX(Data!$H$1:$H$16200,SMA LL(IF(Data!$E$1:$E$16200=$E$5&$B11,ROW(Data!$H$1:$ H$16200),""),ROW())))) Both Instances the Cell is blank.  Thanks, Ben "Anthony Slater" wrote: I use the following for something similar. It might be what you are looking for:  =IF(ISERROR(INDEX(B1:B12500,SMALL(IF(A1:A12500=D1, ROW(B1:B12500),""),ROW()))=FALSE),"",(INDEX(B1:B12 500,SMALL(IF(A1:A12500=D1,ROW(B1:B12500),""),ROW() )))) Enter as Array Formula CTRL+SHIFT+ENTER Copy this down as many rows as you think there are unique numbers (say 100 rows) Column A would be your Invoice Numbers Column B would be your Purchase order Numbers D1 would be the Purchase order number you are searching for "Abhijeet" wrote: hi , i have data stored in excel as column a Purchase order no column b Invoice no i want to query basis purchase order no & result should give mutiple invoice no stored agst one purchase order no how do i do this using lookup or something ! 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Missing values in Excel Line Chart  Charts and Charting in Excel  
How do I stop excel replacing numerical values with the date?  Excel Discussion (Misc queries)  
in excel, how do I find which values doesn't have a pair?  Excel Discussion (Misc queries)  
excel values from + to   Excel Discussion (Misc queries)  
Excel crashing when typing VLOOKUP  Excel Discussion (Misc queries) 