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 !

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 !

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

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 !

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 !