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 !

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:B12500,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...scapeXSPAM.com


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,SMALL(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,SMALL(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:B12500,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" <[email protected]> wrote in message
news:[email protected]...
> 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,SMALL(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,SMALL(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:B12500,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 !