Remember Me?

 Abhijeet Posts: n/a 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 !

 Anthony Slater Posts: n/a 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 !

 Dave Peterson Posts: n/a How about a User Defined Function?
This returns a list separated by commas to a single cell.

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
 Ben Posts: n/a 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 !

 Biff Posts: n/a 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 !

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post mw55309 Charts and Charting in Excel 1 January 19th 05 12:10 PM Becca C Excel Discussion (Misc queries) 2 December 21st 04 12:12 PM jackies_place Excel Discussion (Misc queries) 2 December 17th 04 05:43 PM jjshariff Excel Discussion (Misc queries) 2 December 2nd 04 09:08 PM Nick Excel Discussion (Misc queries) 2 December 2nd 04 02:27 PM

All times are GMT +1. The time now is 02:57 PM. Copyright ©2004-2019 ExcelBanter.